[SOLVED] != query help

[digitalgod]

hey guys,

 

ok not sure how I'll explain this but here goes I have 2 tables

 

members

+-------+-------------+---------------+

|  id      |  username    |       email        |

+-------+-------------+---------------+

| 1       |    user1        | blah@blah.com |

| 2       |    user2        | 123@123.com  |

| 3       |    user3        | abc@abc.com  |

+-------+-------------+---------------+

 

friends

+-------+-------------+---------------+

|  id      |    owner       |      member     |

+-------+-------------+---------------+

|  510    |    18489      |         3           |

+-------+-------------+---------------+

 

I want to select all the members that match an array of emails like so

 

SELECT id,username
FROM members
WHERE email
IN (
'$list'
)

 

but at the same time I don't want it to return results for entries that are in the friend table. So say that my id is 18489 and that I'm looking for blah@blah.com, 123@123.com and abc@abc.com it will only return id 1 and 2 because id 3 is in the table friends and I ("owner" 18489) am the owner.. what do I have to add in my initial query in order to achieve that

 

don't know if that makes any sense...

 

any help would be greatly appreciated!

[digitalgod]

SELECT id,username
FROM members
WHERE email
IN (
'$list'
)
AND id NOT
IN (								
SELECT members
FROM friends
WHERE owner =18489
)

 

thanks sasa!

[fenway]

A LEFT JOIN is also an option here...

[digitalgod]

hey fenway,

 

I tried using a left join but I kept getting double  entries ( if not more), most likely my query wasn't good.

 

which option would save more resources?