LEFT INNER JOIN WITH GROUP BY 1

[cristi22]

Hello all –

Thanks for your help!

I need to create a result table where members have earnings in 2002 & nothing in 2003 or 2004.

Here is my input data:

SSN       WK         NAME                                     EARN       
--------- ---------- ---------------------------------------- -----------
999999999 04/01/2002 HUGO MORINIGO                                2520.00
999999999 06/01/2002 HUGO MORINIGO                                2520.00
999999999 07/01/2002 HUGO MORINIGO                                2268.00
999999999 11/01/2002 HUGO MORINIGO                                2142.00
999999999 12/01/2002 HUGO MORINIGO                                1890.00
999999999 01/01/2003 HUGO MORINIGO                                2520.00
999999999 08/01/2002 HUGO MORINIGO                                2520.00
999999999 09/01/2002 HUGO MORINIGO                                2394.00
999999999 10/01/2002 HUGO MORINIGO                                2520.00
999999999 02/01/2003 HUGO MORINIGO                                2394.00
999999999 03/01/2003 HUGO MORINIGO                                1890.00

Somehow I need to combine the following 2 statements

select ssn, year(wk) , name , sum(earn) from SSA_363EP where 
YEAR(WK) =  YEAR (DATE('2002-01-01')) 
group by ssn, year(wk), name

select ssn, year(wk), name, sum(earn)  from SSA_363EP where 
YEAR(WK) =   YEAR (DATE('2003-01-01')) or YEAR(WK) = YEAR (DATE('2004-01-01')) 
group by ssn, year(wk),name

I think I am no the right track, although the following select returns 0 records (should be 36)

select L.ssn, year(L.wk) , l.name , sum(L.earn) from SSA_363EP L LEFT OUTER JOIN  SSA_363EP R 
on l.ssn=r.ssn 
where 
YEAR(L.WK) =  YEAR (DATE('2002-01-01'))  
and YEAR(r.WK) =  YEAR (DATE('2003-01-01'))   and r.ssn is null
group by L.ssn, year(L.wk), L.name

really appreciate your help,
Cristi

 

[blom0344]

Cristi,

Try a subquery:

select ssn, year(wk) , name , sum(earn) from SSA_363EP where 
YEAR(WK) =  2002 AND ssn not in (select ssn from SSA_363EP where year(WK) in (2003,2004))
group by ssn, year(wk), name

Can be too slow for very big table, though




Ties Blom
Information analyst

 

[cristi22]

Blom, thank you very much for your help!!!