Hi ,
I am in a scenario where I have to perform a select on 2 tables . Its like

Table 1 Table 2

---------------- --------------
ID || Name ID || Amount
---------------- -----------------
0 || ABC 0 || 50000
1 || XXX 1 || 0
2 || ADO 2 || 0
3 || ZZZ 3 || 10000
4 || ddd 4 || 0
5 || xxxx
6 || test

I have to select all values from table 1 whose amount is zero in table 2 along with all the values who are missing in table 2 as well . I dont need to select any values which are included in table 2 and whose amount is >0.
The constraint is on the id on the table in Table 2.

I tried using an outer join on my query, but that is returning all results from table 1 which have amount > 0 as well because they are existing in Table 1 .

Here is my query , any clue guys ?


select a.* , b.* from administrator.clmplaheader as a
LEFT OUTER JOIN administrator.clmclmentry as b on
(a.CLMPLANO = b.clmplano AND b.clmlossamt <=0)
where
a.clmpladate between '04-02-2009' and '07-03-2009' ;

Did you try moving "b.clmlossamt <=0" to WHERE clause ?

thanks for the reply ,

I tried that and its just returning me those records which have amount = 0 and not all those which are in table 1 and not in table2. Similar is the case with and INNER join.

Its got a bit confusing to me

This one ???

select a.* , b.*
from
administrator.clmplaheader as a
LEFT OUTER JOIN administrator.clmclmentry as b on (a.CLMPLANO = b.clmplano)
where
a.clmpladate between '04-02-2009' and '07-03-2009'
AND COALESCE(b.clmlossamt ,0) <= 0;