CODEWITHSUNDEEP
mysqlsql
ivodvb
ivodvb

Reputation: 1164

MySQL IF in SELECT

I have three MySQL InnoDB tables: Debtors Companies Private individuals

Now I would like to find information about a debtor. The following SQL is not working, can anyone help me with writing working SQL?

SELECT
    d.id,
    d.type,
    i.name
FROM
    debtors AS d
IF d.type = 'c' THEN
    INNER JOIN
        companies AS i ON (i.debtor_id = d.id)
ELSE THEN
    INNER JOIN
        private_individuals AS i ON (i.debtor_id = d.id)
WHERE
    d.id = 1

Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF d.type = 'c' THEN INNER JOIN companies AS i ON (i.debtor_id = d.i' at line 7

Thanks in advance!

Upvotes: 2

Views: 148

Answers (2)

aF.
aF.

Reputation: 66697

You can't use the IF like that!

Here is a possible solution:

SELECT
    d.id,
    d.type,
    COALESCE(i.name, i2.name) as name
FROM
    debtors AS d
    LEFT JOIN companies AS i ON i.debtor_id = d.id and d.type = 'c'
    LEFT JOIN private_individuals AS i2 ON i2.debtor_id = d.id and d.type <> 'c'
WHERE
    d.id = 1

Another might be Dynamic SQL but you should avoid it! :)

Upvotes: 4

Marcus Adams
Marcus Adams

Reputation: 53830

This is how you would usually accomplish something like that:

SELECT d.id, d.type, COALESCE(c.name, p.name)
FROM debtors d
LEFT JOIN companies c
  ON d.type = 'c' AND c.debtor_id = d.id
LEFT JOIN private_individuals p
  ON d.type = 'p' AND p.debtor_id = d.id
WHERE d.id = 1

Upvotes: 3

Related Questions