CODEWITHSUNDEEP
mysqlsql
Poramate Homprakob
Poramate Homprakob

Reputation: 11

How to select from table depend on value from another table column

How to make sql statement to select which table depend on value from some another table like this:

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

some part of tables look like this:

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

if type = 0, transaction_id means claim.id, else retrieve.id

so the outputs may be like:

| 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 |

Upvotes: 0

Views: 1561

Answers (3)

Mindaugas Tamosevicius
Mindaugas Tamosevicius

Reputation: 172

Two simple joins and simple filter is much better than one complex join.

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

Alternative- join both tables with outer join, and use filter in the column:

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'

Upvotes: 2

Poramate Homprakob
Poramate Homprakob

Reputation: 11

I try to specify and name each column from both table to be matched and also replace a missing column in retrieve table with NULL like this:

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

that is work for me right now.

Any better ways?

Upvotes: 0

Tathagat Verma
Tathagat Verma

Reputation: 558

I'd recomment using a dynamic SQL query. This would make the SQL running logic simpler, readable and easy to maintain.

Upvotes: 0

Related Questions