CODEWITHSUNDEEP
mysql
user2682722
user2682722

Reputation:

MySQL LEFT JOIN with IF

I have this query:

SELECT r.*, d.rateName
FROM ratings r
LEFT JOIN rate_data d ON (r.rateID=d.rateID AND d.rateName != '')
ORDER BY r.rateTime DESC

This work fine. Now I have a second data table. Give a change to make this with an IF in one query, or is the only way to make two queries and merge the result?

I try to show you what I mean:

SELECT r.*, d.rateName
FROM ratings r
(IF r.isOther == 0)LEFT JOIN rate_data d ON (r.rateID=d.rateID AND d.rateName != '')
(ELSEIF r.isOther == 1)LEFT JOIN rate_data_other d ON (r.rateOtherID=d.rateOtherID AND d.rateName != '')
ORDER BY r.rateTime DESC

Thank you for help

Upvotes: 3

Views: 1061

Answers (1)

zakhefron
zakhefron

Reputation: 1443

SELECT r.*, IF(r.isOther,do.rateName,d.rateName)
FROM ratings r
LEFT JOIN rate_data d ON (r.rateID = d.rateID AND d.rateName != '')
LEFT JOIN rate_data_other do ON (r.rateOtherID = do.rateOtherID AND do.rateName != '')
ORDER BY r.rateTime DESC;

Or

SELECT r.*, 
(CASE 
 WHEN r.isOther = 0 THEN d.rateName
 ELSE do.rateName
 END) as rateName
FROM ratings r
LEFT JOIN rate_data d ON (r.rateID = d.rateID AND d.rateName != '')
LEFT JOIN rate_data_other do ON (r.rateOtherID = do.rateOtherID AND do.rateName != '')
ORDER BY r.rateTime DESC;

Upvotes: 2

Related Questions