SQL Syntax help

[kim]

Here is my situation:

Table A has three columns (aaa1, aaa2, aaa3)
Table B has three columns (bbb1, bbb2, bbb3)

Common field is aaa1 = bbb1

I write a query that retrieves 3 rows out of table a
(select aaa2, aaa3
from table a
where (aaa2 = green) and (aaa3 = yellow))
----------------------------------------------------------
When I now create a left outer join onto Table b, and add one more additional criteria to the where exisiting ones(aaa2=green,aaa3=yellow) which is bbb2='white', I expect to at least see my original three rows of data (since of my left outer join), but now only receive 2 rows of data, since the third row does not have bbb2 as white.

Im confused...I was expecting to see three rows of data, with the bbb2 column being null for the third row....but instead I only get two rows of data...how can I write my query that will give me what I expected ?

Thank you

[andi_g69]

What I understand your query look like this:

(select aaa2, aaa3
from tablea
left join tableb on tablea.aaa1 = tableb.bbb1
where (aaa2 = green) and (aaa3 = yellow) and (bbb2='white'))

By adding the 3rd condition you turn the left join into an inner join because the qury will only return records from table a hich do have a matching record in tableb.

Try this instead:
(select aaa2, aaa3
from tablea
left join tableb on (tablea.aaa1 = tableb.bbb1 and (tableb.bbb2='white'))
where (aaa2 = green) and (aaa3 = yellow))

[kim]

EXCELLENT !!!

Vielen Dank !