I try to do an INNER JOIN depending of the result of CASE.
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
I have see some examples on this site, but nothing work in my case. Someone can help me?
Best How To :
- 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
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
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.