[SOLVED] MYSQL Query HELP

[rocoso]

Hi

This query will select email address of members that have a picture.

SELECT email FROM users a, useralbum b WHERE a.user_id=b.zuserid AND b.picnumber ='1'

 

I would like to select email addresses of members who do not have a picture.

b.picnumber ='0'  or something like that is not going to work because if they never uploaded a picture they have no records in the useralbum table.

 

Thanks

 

 

 

 

 

[rajivgonsalves]

Here you go

 

SELECT email FROM users where userid not in (select user_id from useralbum)

[toplay]

Here you go

 

SELECT email FROM users where userid not in (select user_id from useralbum)

 

rajivgonsalves, based on rocoso post it would be this:

 

SELECT email FROM users where user_id not in (select zuserid from useralbum)

 

 

Also, here's another way:

 

SELECT
             u.email
   FROM
             `users` u
   LEFT JOIN
             `useralbum` ua
     ON
             ua.zuserid = u.user_id

WHERE   
             ua.zuserid IS NULL
;

 

[rajivgonsalves]

sorry did not see that  the left join looks cool I learnt something 

[rocoso]

This worked!

 

SELECT email FROM users where user_id not in (select zuserid from useralbum)

 

 

gotta study SELECT within SELECT

 

 

Thanks you guy are the best:)

[Barand]

JOINS, like Toplay used, are generally more efficient than subqueries.

[rocoso]

Thats very true!  on the production server that had a older mysql version it totally bomb out.  The LEFT JOIN action is the way to go...