All A LEFT OUTER JOIN ON B but (with a filter on B)

Question

All A LEFT OUTER JOIN ON B but (with a filter on B)

saintor1

Mr or Mrs. 500

Points: 562
More actions
June 15, 2017 at 7:14 pm

#343250

This is the query.

SELECT   InvMaster.StockCode, InvMaster.Description, AdmFormData.KeyField, AdmFormData.FieldName
FROM    InvMaster LEFT OUTER JOIN
         AdmFormData ON InvMaster.StockCode = AdmFormData.KeyField
WHERE   (AdmFormData.FieldName = 'NAFTA')
I want all the StockCode from InvMaster.   There might be an equivalent record in AdmFormData or not. And when there are one, I want only the ones with a FieldName filtered to 'NAFTA'.
Of course when I run it, I don't get all the StockCodes from Master but only the ones with record that match the filter in AdmFormData. I did a scalar function but it is slow as hell.
What is the best way to address this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 1

SELECT InvMaster.StockCode, InvMaster.Description, AdmFormData.KeyField, AdmFormData.FieldName FROM InvMaster LEFT OUTER JOIN AdmFormData ON InvMaster.StockCode = AdmFormData.KeyField WHERE (AdmFormData.FieldName = 'NAFTA') or (AdmFormData.FieldName IS NULL)

EDIT: I was under impression that adding the code in bold worked but it didn't. I was seeing some null value in the second table and I assumed that it was because they weren't existent. But the real reason was that the records did exist in the second table with a matching keyfield, but the field values that I was looking for were NULL.

So I am back to square one.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

The query you posted as your answer does not do what you verbally described as your requirement.

It will only return rows where AdmFormData.FieldName is either 'NAFTA' or NULL.

If FieldName is anything else, the row will be removed, meaning you won't get every StockCode from InvMaster.

What you describe as your requirement is actually just adding AdmFormData.FieldName='NAFTA' to your join criteria, i.e.:

SELECT InvMaster.StockCode, InvMaster.Description, AdmFormData.KeyField, AdmFormData.FieldName FROM InvMaster LEFT OUTER JOIN AdmFormData ON InvMaster.StockCode = AdmFormData.KeyField AND AdmFormData.Fieldname='NAFTA';

Cheers!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'd commonly use the option described by Jacob. However, if the conditions are too complex, I use a CTE or subquery.
--Option 1 SELECT m.StockCode, m.Description, fd.KeyField, fd.FieldName FROM InvMaster m LEFT JOIN (SELECT * FROM AdmFormData WHERE FieldName = 'NAFTA') fd ON m.StockCode = fd.KeyField;

--Option 2 WITH cteFormData AS( SELECT * FROM AdmFormData WHERE FieldName = 'NAFTA' ) SELECT m.StockCode, m.Description, fd.KeyField, fd.FieldName FROM InvMaster m LEFT JOIN cteFormData fd ON m.StockCode = fd.KeyField;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 4

Luis Cazares - Friday, June 16, 2017 7:02 AM
I'd commonly use the option described by Jacob. However, if the conditions are too complex, I use a CTE or subquery.
--Option 1 SELECT m.StockCode, m.Description, fd.KeyField, fd.FieldName FROM InvMaster m LEFT JOIN (SELECT * FROM AdmFormData WHERE FieldName = 'NAFTA') fd ON m.StockCode = fd.KeyField;
--Option 2 WITH cteFormData AS( SELECT * FROM AdmFormData WHERE FieldName = 'NAFTA' ) SELECT m.StockCode, m.Description, fd.KeyField, fd.FieldName FROM InvMaster m LEFT JOIN cteFormData fd ON m.StockCode = fd.KeyField;

Option 1 works nicely. Thank you very much.