Link to home
Create AccountLog in
Avatar of ttta83
ttta83

asked on

DB2 - LEFT JOIN statement

I have 2 DB2 tables


Table TRNG_COURSE

TRNG_COURS_CODE   TRNG_COURS_TITLE
1234      Course 1
5678      Course 2

and

Table EMPL_TRNG_COURS
EMPL_ID   TRNG_COURS_CODE   COURS_COMP_DATE
11      1234      2/13/2012
11      5678      2/11/2012
12      5678      10/18/2012
13      1234      05/01/2012
13      5678      05/02/2012
14      1234      7/14/2012

Please help me to write the SQL to display both courses for each EMPL_ID, COURS_COMP_DATE will be blank if there's no record in EMPL_TRNG_COURS table.

The result should look like this

EMPL_ID     TRNG_COURS_CODE     COURS_COMP_DATE
11      1234      2/13/2012
11      5678      2/11/2012
12      1234      
12      5678      10/18/2012
13      1234      05/01/2012
13      5678      05/02/2012
14      1234      7/14/2012
14      5678      

Thanks,
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi ttt,

This one's actually very easy.  :)

Table TRNG_COURSE

Table EMPL_TRNG_COURS

Start with:

SELECT *
FROM empl_trng_cours t0
LEFT JOIN trng_course t1
  ON t0.empl_id = t1.empl_id
order by t0.empl_id, trng_course_code;


Then just select the things that you really want (and in the correct order)

SELECT *t0.empl_id, trng_course_code, TRNG_COURS_CODE, COURS_COMP_DATE
FROM empl_trng_cours t0
LEFT JOIN trng_course t1
  ON t0.empl_id = t1.empl_id
order by t0.empl_id, trng_course_code;


Good Luck,
Kent
Avatar of ttta83
ttta83

ASKER

Hi Kent,

I have to do the left join on TRNG_COURS_CODE 'cause t1 doesn't have EMPL_ID


SELECT t0.empl_id, t1.TRNG_COURS_CODE, t0.COURS_COMP_DATE
FROM db2dba.empl_trng_cours t0
LEFT JOIN db2dba.trng_cours t1
  ON t0.trng_cours_code = t1.trng_cours_code
where t1.TRNG_COURS_CODE IN ('1234','5678')
order by t0.empl_id, t0.trng_cours_code

It only returns 1 row for EMPL_ID 12 and 14.  I need 2 courses 1234 and 5678 for each EMPL_ID

EMPL_ID     TRNG_COURS_CODE     COURS_COMP_DATE
11      1234      2/13/2012
11      5678      2/11/2012
12      5678      10/18/2012
13      1234      05/01/2012
13      5678      05/02/2012
14      1234      7/14/2012
Ok.  I misread the titles.  :)

How is that anything other than EMPL_TRNG_COURS, sorted by employee and course code?
Avatar of ttta83

ASKER

The TRNG_COURS table has more than 2 courses.  I just want to display course 1234 and 5678 for each empl_ID.  If possible, I want to pass to the SQL the EMPL_ID so we can display 2 rows.  If I pass in EMPL_ID 11, I should get

EMPL_ID     TRNG_COURS_CODE     COURS_COMP_DATE
11      1234      2/13/2012
11      5678      2/11/2012

If I pass in EMPL_ID 12, I should get

EMPL_ID     TRNG_COURS_CODE     COURS_COMP_DATE
12      1234      
12      5678      10/18/2012

If I pass in EMPL_ID 99, since EMPL_ID 99 is not in table EMPL_TRNG_COUSE, I should get

EMPL_ID     TRNG_COURS_CODE     COURS_COMP_DATE
99      1234      
99      5678
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of ttta83

ASKER

Thank you very much for your help.

When you get a chance, would you please also help me on how to display multiple records in 1 row.  I have open question here

https://www.experts-exchange.com/questions/27926679/DB2-Display-multiple-records-in-1-row.html

Thanks,