CODEWITHSUNDEEP
mysqlsqljoinif-statement
Josiah
Josiah

Reputation: 83

mySQL LEFT JOIN only if

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

Upvotes: 3

Views: 5120

Answers (4)

PakiPat
PakiPat

Reputation: 1065

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

Upvotes: 1

maimoona
maimoona

Reputation: 627

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

Upvotes: 4

Minesh
Minesh

Reputation: 2302

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 =''

Upvotes: 0

JanR
JanR

Reputation: 6132

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.

Upvotes: 1

Related Questions