Ricky Ricky - 3 months ago 11
SQL Question

How to display data in JOIN, and return null value?

How to join two tables and get return null value if not available value in another table?

I tried to join with many examples that I found, but I've had trouble getting the hoped. Thanks!

Screenshoot

Answer

Use LEFT JOIN:

SELECT
    VA.COL_PERSONNEL_NUMBER,
    VA.COL_CLOCK_DATE,
    VA.P10,
    VA.P20,
    VR.NAME,
    VR.UNIT
FROM VIEW_ABSEN VA
LEFT JOIN VIEW_REPORT VR ON VA.COL_PERSONNEL_NUMBER=VR.ID
    AND VR.DATE=VA.COL_CLOCK_DATE
WHERE VA.COL_PERSONNEL_NUMBER LIKE '%521663%'
    AND VA.COL_CLOCK_DATE BETWEEN '2016-08-01' AND '2016-08-05'