Mysql condition based table join

Question

I want to join two tables based on the below scenarios:

1. Need to display all the requirements table values

2. Join the second table(prod_spec) based on the requirement_id and inquiry_id.

Table 1(requirements):

Table 2(prod_spec):

Using below query I'm getting the result below:

SELECT requirements.id,requirements.requirement, prod_spec.evidence,prod_spec.status,prod_spec.no_specify,prod_spec.inquiry_id FROM requirements LEFT JOIN product_spec ON prod_spec.requirement_id=requirement.id

The problem is not getting any result when I put the where condition eg: where inquiry_id='67' to the sql query. Please let me know how to display the rows based on inquiry_id.

FYI, requirement_id will only there once in prod_spec table based on inquiry_id.

Answer 1

The problem is not getting any result when I put the where condition eg: where inquiry_id = '67' to the sql query

The condition on the LEFT JOINed table needs to go to the ON clause of the JOIN:

SELECT r.id,r.requirement, ps.evidence,ps.status, ps.no_specify, ps.inquiry_id 
FROM requirements r
LEFT JOIN prod_spec 
    ON  ps.requirement_id = requirement.id 
    AND ps.inquiry_id = 67   --> here

Rationale: if you put that condition in the WHERE clause, then it becomes mandatory; as a consequence, rows from requirements for which there is no match in prod_spec are evicted from the resultset.

Side notes:

• table aliases make the query easier to write and read

• it seems like inquiry_id is a number, so it should be compared as such; don't put single quotes around literal 67