join mysql 等价于,MySQL查询，其中JOIN取决于情况

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?

解决方案

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