mysql - 如何从表中选择取决于另一个表列的值

Question

如何使用sql语句来选择哪个表依赖于另一个表的值，如下所示:

SELECT b.*, detail.*, t.date
FROM board AS b, transaction AS t,
 (CASE t.type
   WHEN 0 THEN (SELECT * FROM claim)
   WHEN 1 THEN (SELECT * FROM retrieve)
 END) AS detail
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND detail.id=t.transaction_id

表格的某些部分如下所示:

| board | |   transaction   | |   claim   | | retrieve |
|-------| |-----------------| |-----------| |----------|
| ...   | | board_id        | | id        | | id       |
|_______| | transaction_id  | | sender    | | code     |
          | type            | | location  | |__________|
          | date            | |___________|
          |_________________|

如果type = 0，transaction_id表示claim.id，否则retrieve.id

所以输出可能是这样的:

| board.id | ... | claim.id | claim.sender | location  | retrieve.id | retrieve.code |       date       |
|----------|-----|----------|--------------|-----------|-------------|---------------|------------------|
| 19       | ... | 10       | SenderA      | locationA |             |               | 10/09/2015 18:09 |
| 19       | ... |          |              |           | 8           | 58/03165      | 14/09/2015 11:10 |
| 19       | ... | 14       | SenderB      | locationA |             |               | 20/09/2015 08:10 |

Answer 1

两个简单的联接和简单的过滤器比一个复杂的联接要好得多。

SELECT b.*, c.number, c.location, t.type, t.date
FROM board AS b, transaction AS t, claim AS c
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND c.id=t.transaction_id AND t.type=0

UNION ALL

SELECT b.*, r.number, NULL AS location, t.type, t.date
FROM board AS b, transaction AS t, retrieve AS r
WHERE b.sn='D92AD006325' AND b.id=t.board_id AND r.id=t.transaction_id AND t.type=1

替代方案 - 使用外连接连接两个表，并在列中使用过滤器:

SELECT b.*, 
       case when t.type = 0 then c.number else r.number end as number, 
       case when t.type = 0 then c.location else null end as location, 
       t.type,
       t.date
FROM board AS b
INNER JOIN transaction AS t,
   ON (b.id=t.board_id)
LEFT OUTER JOIN claim c
   ON (c.id=t.transaction_id and t.type = 0)
LEFT OUTER JOIN retrieve r
   ON (r.id=t.transaction_id and t.type = 1)
WHERE b.sn='D92AD006325'