[SOLVED] return data thats not found in another table

[nexe]

hi I have the following tables

 

Subscribers table

ID | Email

1  | email1@email.com

2  | email2@email.com

3  | email3@email.com

4  | email4@email.com

 

and a table that hold information about what subscribers are attached to what lists so a subscriber can be assigned to more then one list.

ID | subscriber_id | listid

1  |        3          |  1

2  |        2          |  1

3  |        1          |  1

 

 

Now i have the query

select * from subscribers left join sub_lists on subscribers.id = sub_lists.subscribers_id where listid = 1

this all works fine but I'm having trouble working out a query to return all subscribers that are not assigned to a list.

 

somthing like

select * from subscribers left join sub_lists on subscribers.id = sub_lists.subscribers_id where <subscriber.id not in sub_lists>

 

???

[btherl]

select * from subscribers
left join sub_lists on subscribers.id = sub_lists.subscribers_id
where sub_lists.subscriber_id is null

 

There you go   You can use any column from the right table when checking for null from a left join .. I use the joining column for convenience.  The reason is that the left join fills in nulls for all columns of the right table when there is no match.

[nexe]

omg you champion

 

I knew it was worth asking.

 

many many thanks