left join

[M.O.S. Studios]

Im sure this is easy, but this is what i have

 

index			email			user 
207			123@hotmail.com		jay1
208			123@hotmail.com		jay2

 

SELECT count(em.email) email, count(us.user) users FROM members em LEFT JOIN members us ON em.email = '123@hotmail.com' AND us.user = 'jay1'

 

i want it to return

 

EMAIL    USER

2            1

 

 

 

thanks in advance

[fenway]

Why e-mail = 2? They are different users.

[M.O.S. Studios]

its counting the amount of times that email is used

 

basically it would be joining these two queries

 

SELECT count(em.email) email FROM members em WHERE em.email = '123@hotmail.com'

 

to this one

 

SELECT count(us.user) users FROM members us WHERE us.user = 'jay1'

[fenway]

Oh, I see know -- that's a strange query.

 

Easiest way is to switch your WHERE to an OR, and then use IFs to check the boolean condition.

 

SELECT 
SUM( IF( em.email = '123.hotmail.com', 1, 0 ) ) AS email, 
SUM( IF( us.user = 'jay', 1, 0 ) ) AS users 
FROM members em LEFT JOIN members us ON em.email = '123@hotmail.com' OR us.user = 'jay1'

 

But that's not ideal for index usage -- why not just UNION the results?

[M.O.S. Studios]

Never heard of union before,

 

after doing some research i think this is perfect.

 

this is what i came up with

 

(SELECT count(em.email) email FROM members em WHERE email = '123.hotmail.com') UNION (SELECT count(us.user) user FROM members us WHERE user = 'jay1')

 

only problem is that its returning the two values under one column, any idea why

[fenway]

That's not a problem per se.  Add a column labeled "type" to your output, and then in php you can format it however you desire.  Alternatively, I suppose you can use each one a scalar subquery, too.