Using a Left Join

The LEFT JOIN keyword returns all rows from the left table (table1, also referred to as the primary table on occasion), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.



SYNTAX:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;



%{=====================================================%}
%{============== Get Project Data =====================%}
%{=====================================================%}

%FUNCTION(DTW_SQL) GetProject()  {

  SELECT HDRPRJ, HDRSUB, HDRSTS, HDRDTE, HDRMDT,
    substr(DIGITS(HDRDTE),5,2) concat '/' 
	concat substr(DIGITS(HDRDTE),7,2) concat '/' 
	concat substr(DIGITS(HDRDTE),3,2) as STDATE,
    substr(DIGITS(HDRMDT),5,2) concat '/' 
	concat substr(DIGITS(HDRMDT),7,2) concat '/' 
	concat substr(DIGITS(HDRMDT),3,2) as MLDATE,
    HDRFLY, HDRCMP, HDRLSH, HDRDSC, STDSTS, STDDSC

  FROM PNAHDRPF LEFT JOIN PNASTDPF ON PNAHDRPF.HDRSTS = PNASTDPF.STDSTS

  WHERE  HDRPRJ=$(PRJ) AND HDRSUB='$(SUB)' 

      %MESSAGE {     100  :  { @Write_Return() %}  : exit  %}