I'm trying to get all rows from two tables by joining.
When there is no corresponding row found in TABLEB, I'm trying to get blanks in the output but my query is not giving desired results
Please advise.
TABLEA - sample data and this table has more rows than this
Hi Krishna,
I belive you want to write all rows from Table A and when you find a match in Table B write amounts from table B in report else populate spaces for.
Here you need to use left outer join as below
Code:
SELECT A.B_OFF#
,A.ID_USER
,B.CHK_#
, B.CHK_AMT
FROM TABLEA.A
LEFT OUTER JOINT
TABLEB.B
ON A.P_ID = B.P_ID
AND ID_USER IN (761001,
761002,
760996,
760997,
760998)
;
When B.CHK_# and B.CHK_AMT are null you need to populate Spaces to your corresponding report variables