CODEWITHSUNDEEP
mysqlsql
user2828677
user2828677

Reputation: 27

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!

Upvotes: 1

Views: 52

Answers (1)

jarlh
jarlh

Reputation: 44746

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"

Upvotes: 2

Related Questions