CODEWITHSUNDEEP
mysqlselectcaseinner-join
Francis B. Bélisle
Francis B. Bélisle

Reputation: 47

INNER JOIN with 2 CASE

I try to do an INNER JOIN depending of the result of CASE.

SELECT *
FROM album
    INNER JOIN (
                SELECT album_type CASE album.album_type 
                                           WHEN 1 THEN "album_int" 
                                           WHEN 2 THEN "album_ext" 
                                  END FROM album)
AS type ON type.album_id = album.id
WHERE album.id = 6
LIMIT 1;

I have see some examples on this site, but nothing work in my case. Someone can help me?

Upvotes: 0

Views: 72

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35583

  • I have 3 tables (album, album_int and album_ext).
  • I want to join album_int or album_ext to album.
  • if album.album_type = 1 I join album_int,
  • else if album.album_type = 2 I join album_ext.
  • In album_int and album_int I have a column name album_id (same unique id in album)

You do not need any case expressions or even a where clause.

SELECT a.`id`, COALESCE(aint.`something`, aext.`something`) as something, ...
FROM ALBUM A
LEFT OUTER JOIN ALBUM_INT AS AINT ON A.ID = AINT.ALBUM_ID AND A.ALBUM_TYPE = 1
LEFT OUTER JOIN ALBUM_EXT AS AEXT ON A.ID = AEXT.ALBUM_ID AND A.ALBUM_TYPE = 2

AND A.ALBUM_TYPE = 1 as a join condition means that ALBUM_INT will ONLY join to ALBUM rows where album_type = 1

similarly:

AND A.ALBUM_TYPE = 2 as a join condition means that ALBUM_EXT will ONLY join to ALBUM rows where album_type = 2

please note that an answer previously given by Gordon Linoff uses the same join logic, I have just attempted to emphasize how it meets the described requirements.

Upvotes: 2

Erik Blessman
Erik Blessman

Reputation: 693

To Join to one of two tables conditionally, create a join column on each table and UNION them together as such:

SELECT *
FROM    albums a
INNER JOIN (
    SELECT 1 AS album_type, album_id, colA, colB
    FROM album_int
    UNION ALL
    SELECT 2 AS album_type, album_id, colA, colB
    FROM album_ext ) AS b
    ON a.album_type = b.album_type
    AND a.album_id = b.album_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

case doesn't work on tables. You can use left join:

SELECT *
FROM album a LEFT JOIN
     album_int ai
     ON ai.album_id = a.id AND a.album_type = 1 LEFT JOIN
     album_ext ae
     ON ae.album_id = a.id AND a.album_type = 2 
WHERE a.id = 6 AND a.album_type IN (1, 2)
LIMIT 1;

To get values into the same column, you then need to use coalesce().

Note: This does the right thing if there is at most one match in each table.

Upvotes: 2

Related Questions