MySQL query where JOIN depends on CASE

Question

As I am now aware, CASE can be used only in WHERE context. Though, I need to use different table depending on column value. What I've tried looks like this:

SELECT
    `ft1`.`task`,
    COUNT(`ft1`.`id`) `count`
FROM
    `feed_tasks` `ft1`
CASE
    `ft1`.`type`
WHEN
    1
THEN
    (INNER JOIN `pages` `p1` ON `p1`.`id` = `ft1`.`reference_id`)
WHEN
    2
THEN
    (INNER JOIN `urls` `u1` ON `u1`.`id` = `ft1`.`reference_id`)
WHERE
    `ft1`.`account_id` IS NOT NULL AND
    `a1`.`user_id` = {$db->quote($user['id'])}

Now that I know this is invalid syntax, what's the closest alternative?

Where did you read that CASE can only be used in WHERE clauses? It isn't true. — Álvaro González, Aug 09 '11 at 10:39
merge as in literally "merge two databases into one"? well, in that case this isn't an option because they both have completely different structure. @Álvaro G. Vicario: either way, this isn't valid syntax. Therefore I am asking for an alternative. — Gajus, Aug 09 '11 at 10:40
`merge two TABLES into one TABLE`,completely different? at least both tables having column `user_id` and `reference_id`, so where is the completely? — ajreal, Aug 09 '11 at 10:47

Álvaro González · Accepted Answer · 2014-12-18T10:30:12.087

15

It probably needs tweaking to return the correct results but I hope you get the idea:

SELECT ft1.task, COUNT(ft1.id) AS count
FROM feed_tasks ft1
LEFT JOIN pages p1 ON ft1.type=1 AND p1.id = ft1.reference_id
LEFT JOIN urls u1 ON ft1.type=2 AND u1.id = ft1.reference_id
WHERE COALESCE(p1.id, u1.id) IS NOT NULL
AND ft1.account_id IS NOT NULL
AND a1.user_id = :user_id

Edit:

A little note about CASE...END. Your original code does not run because, unlike PHP or JavaScript, the SQL CASE is not a flow control structure that allows to choose which part of the code will run. Instead, it returns an expression. So you can do this:

SELECT CASE
    WHEN foo<0 THEN 'Yes'
    ELSE 'No'
END AS is_negative
FROM bar

... but not this:

-- Invalid
CASE 
    WHEN foo<0 THEN SELECT 'Yes' AS is_negative
    ELSE SELECT 'No' AS is_negative
END
FROM bar

edited Dec 18 '14 at 10:30

answered Aug 09 '11 at 10:42

Álvaro González

142,137
41
261
360

it works fine with two left joins but when i add a third choice it return an error – Yosra Nagati May 03 '15 at 11:46
@YosraNagati There's nothing in the join syntax that restricts it to a given number of tables. The error message is probably due to an error in your code. – Álvaro González May 04 '15 at 11:30
I stand corrected: in MySQL you can only JOIN [61 tables at a time](https://stackoverflow.com/questions/1684971/too-many-tables-mysql-can-only-use-61-tables-in-a-join). – Álvaro González Jun 29 '19 at 08:51

score 1 · Answer 2 · answered Aug 09 '11 at 10:39

Use outer joins on both tables and move the CASE inside your COUNT:

SELECT
    ft1.task,
    COUNT(case ft1.id when 1 then p1.id when 3 then u1.id end) as count
FROM feed_tasks ft1
LEFT JOIN pages p1 ON p1.id = ft1.reference_id
LEFT JOIN urls u1 ON u1.id = ft1.reference_id
WHERE ft1.account_id IS NOT NULL
AND a1.user_id = {$db->quote($user['id'])}

Non-hits for the CASE will give a null id and won't be counted.

Note: Table a1 is in your where clause, but doesn't seem to be a selected table

MySQL query where JOIN depends on CASE

2 Answers2

Linked