mySQL LEFT JOIN only if

Question

I'm trying to do a LEFT JOIN only if one of the columns in the table I'm joining = 1.

Here is what I have so far.

SELECT * FROM size, oil LEFT JOIN purchase ON oil. oilId = purchase. purOilId
WHERE sizId = oilSizeId AND oilUnused ='' ORDER BY oilOurName

What I need to add is below.

LEFT JOIN purhcase (only if purMark = 1)

This is what I'm not sure how to do.

It will not work to have an AND later on because the oil table needs to define how many rows are in the result.

Thanks

Answer 1

Add AND condition to the JOIN like below

SELECT * FROM size, oil LEFT JOIN purchase ON oil.oilId = purchase.purOilId AND purchase.purMark = 1  
WHERE sizId = oilSizeId AND oilUnused ='' ORDER BY oilOurName

Answer 2

In mysql the only way I think you could do this is by having a subquery in your join similar to this:

SELECT * FROM size, oil 
LEFT JOIN (
   SELECT * 
   FROM purchase
   WHERE purMark = 1
) purchase ON purchase.purOilId = oil.oilId /* or whatever your joining criteria is */
WHERE sizId = oilSizeId AND oilUnused ='' 
ORDER BY oilOurName

Hope this helps.

Answer 3

Oddly enough, this question was asked and answered here just a week later than it was here

Answer 4

Try below:

SELECT * FROM size, oil 
LEFT JOIN purchase ON oil. oilId = purchase. purOilId
WHERE purMark = 1
sizId = oilSizeId AND oilUnused ='' 
UNION
SELECT * FROM size, oil 
WHERE purMark != 1
sizId = oilSizeId AND oilUnused ='' 

Or

SELECT * FROM size, oil 
LEFT JOIN purchase ON oil. oilId = purchase. purOilId AND purMark = 1
WHERE sizId = oilSizeId AND oilUnused =''