INNER JOIN 2

[cristi22]

Hello all -

DB2 UDB 9

t1 data:

SSN       TYPE EFF_DATE   FUND  
--------- ---- ---------- ------
999999999 NK   02/01/2002 DOH   
888888888 NK   02/01/2002 DOH   
777777777 NK   02/01/2002 DOH   
666666666 NK   02/01/2002 DOH

t2 data

SSN       NAME              PERIOD    EMPLOYER 
-----------------------------------------------
888888888 WILLIAM J GREER  07/01/2002 9990001  
888888888 WILLIAM J GREER  07/01/2002 9990001  
888888888 WILLIAM J GREER  08/01/2002 9990001  
888888888 WILLIAM J GREER  08/01/2002 9990001

select:

SELECT   t1.ssn, t1.type, t2.employer
 FROM  UMASS_NK  T1,  UMASS_CONTR  T2
WHERE  T1.SSN=T2.SSN 
  FETCH FIRST 10 ROWS ONLY

How do I create the result table with unique records from t1 & matching data from t2?
I thought that inner join will do, but...obviously does not work

thanks for your help.

 

[BrianTyler]

I am not totally sure of your problem, but if you mean that you would like to see ALL T1 rows, and any corresponding T2 rows you will need an outer join:

SELECT t1.ssn, t1.type, t2.employer
FROM UMASS_NK T1 left outer join UMASS_CONTR T2
on T1.SSN=T2.SSN

If this is not what you are looking for please post your erroneous results and describe what is wrong.

Brian

 

[cristi22]

Thanks Brian!

There are no errors in the above, but the result of the select statement will create a table with duplicated rows.

How do I create the result table with unique rows. To display rows from the left table (t1) & corresponding employer number from t2

For the result to look like:

SSN       TYPE EFF_DATE   FUND  employer
--------- ---- ---------- ------
999999999 NK   02/01/2002 DOH   123456
888888888 NK   02/01/2002 DOH    555555
777777777 NK   02/01/2002 DOH    666666
666666666 NK   02/01/2002 DOH   777777

instead, now it displays

999999999 NK 02/01/2002 DOH 123456 10 times

THANKS

 

[ddiamond]

Are you just trying to make the output distinct?

There are 2 easy ways to do this.

SELECT  [blue]DISTINCT[/blue] t1.ssn, t1.type, t2.employer
 FROM  UMASS_NK  T1,  UMASS_CONTR  T2
WHERE  T1.SSN=T2.SSN 
  FETCH FIRST 10 ROWS ONLY

Or use could use a group by:

SELECT  t1.ssn, t1.type, t2.employer
 FROM  UMASS_NK  T1,  UMASS_CONTR  T2
WHERE  T1.SSN=T2.SSN 
[blue]GROUP BY t1.ssn, t1.type, t2.employer[/blue] 
  FETCH FIRST 10 ROWS ONLY

 

[cristi22]

thanks all for your help!
GROUP BY did it

 

[cristi22]

with the final result....the performance really sucks
please help!!!!

CREATE PROCEDURE BASYS.UMASS_COVERAGE_CODE (   in in_date date )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
    -- IN_OPTION
------------------------------------------------------------------------
P1: BEGIN

Declare cursor1 cursor with return for
select
t2.employer
, t1.ssn
,  t2.period
, 'NK'
from UMASS_NK t1, UMASS_CONTr t2
where  T1.SSN=T2.SSN
group by t2.employer, t1.ssn, t2.period
having t2.period = date('2005-07-01')
union
select
employer
, member_ssn
, ben_date
, benefit_plan
   from elg_summary where benefit_plan in ('DOHIGH', 'DOSTAN') and ben_date = date('2005-07-01');
-- Declare cursor
    OPEN cursor1;
end p1

 

[ddiamond]

I am assuming you mean the performance of the first half of the union with the group by really sucks. I have a couple of suggestions.

1. Move the condition in the having clause to the where clause. Since your having condition does not contain an aggregate function, it doesn't need to be there. The having clause is always processed last.

2. If performance still sucks, make sure UMASS_CONTr has an index on period. If not, add one.

select
t2.employer
, t1.ssn
,  t2.period
, 'NK'
from UMASS_NK t1, UMASS_CONTr t2
where  T1.SSN=T2.SSN
[blue]   and T2.period = date('2005-07-01')[/blue]
group by t2.employer, t1.ssn, t2.period
union
select
employer
, member_ssn
, ben_date
, benefit_plan
   from elg_summary where benefit_plan in ('DOHIGH', 'DOSTAN') and ben_date = date('2005-07-01');