user2828677 user2828677 - 6 months ago 12
SQL Question

Sql query not returnig null values

I have 2 tables:


  1. req_docs



req_docs

and pers_docs

enter image description here

My sql query:

SELECT *
FROM req_docs
LEFT OUTER JOIN pers_docs ON req_docs.doc_nid = pers_docs.doc_nid
WHERE req_docs.pos_id ="CPT"
AND pers_docs.pers_nid = 6
AND pers_docs.expires <= "2009-09-01"`


It returns only 2 rows. Which is ok, but I need also return not existing matches from pers_docs join as NULL values. Help please!

Answer

When LEFT JOIN, put the right side table's conditions in the ON clause to get true left join behavior! (When in WHERE, you get regular INNER JOIN result.)

SELECT *
FROM req_docs
LEFT OUTER JOIN pers_docs ON req_docs.doc_nid = pers_docs.doc_nid
                          AND pers_docs.pers_nid = 6
                          AND pers_docs.expires <= "2009-09-01"
WHERE req_docs.pos_id = "CPT"
Comments