CODEWITHSUNDEEP
mysqlleft-join
raven_977
raven_977

Reputation: 475

MySQL make a left join depending on a condition

I want to make a LEFT JOIN depending on the field 'recipientType' in the main table. I have a recipientType '0' which says it's a customer and recipientType '1' which means it's a coworker. Depending on the type '0' or '1' I need to create different left joins to select the names from different tables.

Here is my example code, which is not working, but maybe shows what I want.

SELECT wm.*, wmc.name AS categoryName, wmbt.title AS templateTitle, u.firstName, u.lastName FROM word_module_final_doc AS wm 
LEFT JOIN word_module_categorys AS wmc ON wmc.id = wm.categoryId 
LEFT JOIN word_module_basic_templates AS wmbt ON wmbt.id = wm.templateId 
LEFT JOIN user AS u ON u.emailAdress = wm.createdBy 
CASE wm.recipientType WHEN '0' 
THEN 
LEFT JOIN customer AS c ON c.customerId = recipientId
END
WHERE wm.createdBy = @createdBy AND wm.deleted = '0' ORDER by id DESC

Is there any way to make this work?

I hope you understand what I mean because english is not my mother tongue.

Thank you in advance :-)

Upvotes: 0

Views: 687

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562921

No, you can't put joins inside conditions like you're describing. SQL has no syntax or semantics for joining "conditionally." Joins must be fixed before the query begins reading data, so there's no way data values can change the tables involved.

But you can accomplish the logic you need by joining to both, and including the recipientType term in the join condition.

SELECT wm.*, wmc.name AS categoryName, wmbt.title AS templateTitle, u.firstName, u.lastName,
  COALESCE(c1.something, c2.something) AS `something`
FROM word_module_final_doc AS wm
LEFT JOIN word_module_categorys AS wmc ON wmc.id = wm.categoryId
LEFT JOIN word_module_basic_templates AS wmbt ON wmbt.id = wm.templateId
LEFT JOIN user AS u ON u.emailAdress = wm.createdBy
LEFT JOIN customer AS c1 ON c1.customerId = wm.recipientId AND wm.recipientType = '0'
LEFT JOIN coworker AS c2 ON c2.customerId = wm.recipientId AND wm.recipientType = '1'
WHERE wm.createdBy = @createdBy AND wm.deleted = '0' ORDER by id DESC

Logically, wm.recipientId can be 0 or 1 but not both at the same time. So only one of those LEFT JOINs can return a value, and the other will return NULLs.

If there's a common column something that both customer and coworker tables have, you can include such a column in your select-list using COALESCE() as I showed. The first non-NULL argument of COALESCE() is the result of that function.

Upvotes: 3

JSowa
JSowa

Reputation: 10682

It's not possible stricte what you want. You can alternatively add condition in your join clause. If you want to join different tables based on column's value you should use UNION, but in your case it's not necessary.

SELECT wm.*, wmc.name AS categoryName, wmbt.title AS templateTitle, u.firstName, u.lastName FROM word_module_final_doc AS wm 
LEFT JOIN word_module_categorys AS wmc ON wmc.id = wm.categoryId 
LEFT JOIN word_module_basic_templates AS wmbt ON wmbt.id = wm.templateId 
LEFT JOIN user AS u ON u.emailAdress = wm.createdBy 
LEFT JOIN customer AS c ON c.customerId = recipientId AND wm.recipientType '0' 
WHERE wm.createdBy = @createdBy AND wm.deleted = '0' ORDER by id DESC

Upvotes: 1

Northbank75
Northbank75

Reputation: 46

Skip the case and include an AND in your join. Works for me often in SQL Server, I am not able to verify this in MySQL but it should do the job.

SELECT wm.*, wmc.name AS categoryName, wmbt.title AS templateTitle, u.firstName, 
u.lastName FROM word_module_final_doc AS wm 
LEFT JOIN word_module_categorys AS wmc ON wmc.id = wm.categoryId 
LEFT JOIN word_module_basic_templates AS wmbt ON wmbt.id = wm.templateId 
LEFT JOIN user AS u ON u.emailAdress = wm.createdBy 
LEFT JOIN customer AS c ON c.customerId = recipientId and wm.recipientType = 0
WHERE wm.createdBy = @createdBy AND wm.deleted = '0' ORDER by id DESC

Upvotes: 1

Related Questions