Select all but exclude those that match data in a 2nd table from the result

[lancia]

Select table_a.name from table_a where table_b.name = table_a.name & table_b.year = '2009'??

 

 

I'm having trouble explaining the issue, but here goes....

 

table_a

taID | Name

1  | me

2  | you

3  | someone_else

 

table_b

tbID | Name | Year

1    | me    | 2009

2    | someone_else  | 2010

 

 

So my query is:

 

I want to list all names from table_a regardless if they exist in table_b or not (all results), but if they do exist in table_b with the year of '2009' i want to exclude those particular ones from the result.

 

Thanks in advance

[lancia]

Sorry that i'm not very clear on this...

 

for example if i selected 2010 as the year

>select (all results)table_a.name from table_a exclude table_a.name where table_b.name=table_a.name if table_b.year = '2010'

 

the result should come back as

1  | me

2  | you

as according to table_b '2010' is taken up with 'someone_else' hence not displayed in the result

 

 

if i select 2009 as the year

>select (all results)table_a.name from table_a exclude table_a.name where table_b.name=table_a.name if table_b.year = '2009'

 

the result should come back as

1  | you

2  | someone_else

 

[fenway]

You want LEFT JOIN... IS NULL.

[lancia]

Actually Left join ... is null - does not work, After weeks of trial and error this is the correct SQL Statement

 

SOLUTION:

 

Select table_a.name form table_a where NOT EXISTS ((SELECT table_b.name from table_b where table_b.year = '2009') or (table_b.year IS NULL)) order by table_a.name asc

[fenway]

Not certain that LEFT JOIN .. IS NULL wouldn't have work here, but if you have it working, and it's efficient, then ok.