SQL join with Where clause still displays part of record

Question

I have read this question as it seemed similar but it does not quite work for my scenario and I have not been able to adapt this solution to my problem: Left Join With Where Clause

My SQL query currently includes a join on the JOBS table on the column production_id like this:

SELECT md.production_id,manufacture_id, pr.product_code,j.production_id as jobs,j.job_type
FROM MANUFACTURE_DATE md
INNER JOIN PRODUCTS pr
on md.production_id = pr.production_id
LEFT JOIN JOBS j
on md.production_id = j.production_id
WHERE active='1' AND date='2021-09-09'
ORDER BY product_code DESC;

This returns a product_code which has a record in the jobs table with a job_type of 3

On the join with the JOBS table I now only want to include values with an job_type=1 but not job_type=2 or job_type=3 so I have included a WHERE clause in the join as below:

SELECT md.production_id,manufacture_id, pr.product_code,j.production_id as jobs,j.job_type
FROM MANUFACTURE_DATE md
INNER JOIN PRODUCTS pr
on md.production_id = pr.production_id
LEFT JOIN JOBS j
on md.production_id = j.production_id AND j.job_type=1
WHERE active='1' AND date='2021-09-09'
ORDER BY product_code DESC;

This returns the product_code which has a record in the jobs table and displays NULL for jobs.production_id and jobs.job_type

My aim is to not have this product_code displayed at all because it is present in the JOBS table but not with the desired job_type of 1.

It's good programming practice to _qualify_ all columns (at least when several tables are involved.) E.g. `jobs.job_type` instead of just `active` - we don't know which table each column belongs to. — jarlh, Apr 21 '22 at 08:13
@Jeanclaude you seem happy to alias ther other tables with a meaningfully shorter aliases so what's wrong with `j`? — Stu, Apr 21 '22 at 08:22
@Jeanclaude it's difficult to provide answer without sample data, Please try jobs.production_id IS NOT NULL after AND jobs.job_type=1 in where condition — Akash Patel, Apr 21 '22 at 08:25
Is there a case that for the same jobs.production_id there are more than 1 job_types? If not, then change the LEFT join to INNER join. — forpas, Apr 21 '22 at 08:27
LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. Also what (left table) unmatched rows you want. PS [mre] For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Apr 21 '22 at 08:31

Nishant Gupta · Accepted Answer · 2022-04-21T08:58:17.493

The solution for your problem can be in two ways:

Using Inner Join instead of Left Join (Assuming all production_ids present in Manufacture Table are also present in Jobs Table)

 SELECT md.production_id,manufacture_id,
 pr.product_code,jobs.production_id as jobs,jobs.job_type
 FROM MANUFACTURE_DATE md 
 INNER JOIN PRODUCTS pr 
 ON md.production_id = pr.production_id 
 INNER JOIN JOBS jobs 
 ON md.production_id = obs.production_id AND jobs.job_type=1 
 WHERE active='1' AND date='2021-09-09' ORDER BY product_code DESC;

Using same filter condition on job_type but in where clause along with one more condition:

 SELECT md.production_id,manufacture_id,
 pr.product_code,jobs.production_id as jobs,jobs.job_type
 FROM MANUFACTURE_DATE md
 INNER JOIN PRODUCTS pr
 on md.production_id = pr.production_id
 LEFT JOIN JOBS jobs
 on md.production_id = jobs.production_id 
 WHERE active='1' AND date='2021-09-09' 
 AND (jobs.job_type=1 OR jobs.production_id IS NULL)
 ORDER BY product_code DESC;

Why you are going wrong can be explained through sample code example given at below link: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=876eb66e340e27b05ae543238b5a88ff

@Jeanclaude From next time try to provide minimal reproducible example. It will be better for faster resolution of the problem. — Nishant Gupta, Apr 21 '22 at 09:01

SQL join with Where clause still displays part of record

1 Answers1