CODEWITHSUNDEEP
sqljoindb2outputunion
gamefreak249
gamefreak249

Reputation: 85

SQL DB2 - problem with combining rows from 2 tables into one output

I am having issues combining rows from 2 tables, and I can't figure out how to get UNION to work. I have 2 database tables:

TFBCR table contains these columns:

TFBCAI contains none, 1 or many rows depending if the above FBC_STATUS_CD is anything but '00'. It has:

I am trying to show all rows from both tables, even when FBC_STATUS_CD is '00' or anything else.

Right now it is only outputting rows that have the STATUS_CODE anything but 00 because of how I am linking the 2 tables. How can I have something like this:

FBC_REQ_REF_NBR     CLT_ID_A      FBC_STATUS_CD  CLT_ID_B    BALANCE
1                   JOHN          00             --------    --------
2                   ADAM          11             ADAM_1      25.00
2                   ADAM          11             ADAM_2      10.00
2                   ADAM          12             ADAM_3      50.00
3                   SAM           00             -------     --------
4                   DAVE          13             DAVE_1      65.50
4                   DAVE          12             DAVE_2      55.00

This is my SQL:

SELECT
  A.FBC_REQ_REF_NBR,
  A.CLT_ID_A,
  A.FBC_STATUS_CD,
  B.CLT_ID_B,
  B.BALANCE
FROM TFBCR A,
     TFBCAI B
WHERE A.FBC_REQ_REF_NBR = B.FBC_REQ_REF_NBR
ORDER BY A.FBC_REQ_REF_NBR
WITH UR;

I need the single rows from TFBCR that have a 00 status code AND all the rows from TFBCAI (if they exist). I am guessing this is done with some kind of subselect, but I can't figure it out.

Thank you!!

Upvotes: 1

Views: 214

Answers (1)

forpas
forpas

Reputation: 164099

What you need is a LEFT JOIN of TFBCR to TFBCAI:

SELECT
  A.FBC_REQ_REF_NBR,
  A.CLT_ID_A,
  A.FBC_STATUS_CD,
  B.CLT_ID_B,
  B.BALANCE
FROM TFBCR A LEFT JOIN TFBCAI B
ON A.FBC_REQ_REF_NBR = B.FBC_REQ_REF_NBR

Upvotes: 1

Related Questions