CODEWITHSUNDEEP
mysqlsqljoin
Marius DV
Marius DV

Reputation: 163

SQL - JOIN on a preferable value otherwise on a default one

I have two tables:

media_id saved_language
000001 en
000002 en
media_id media_title data_language
000001 Title in English en
000002 Title in English en
000002 Title in Italian it

I want to JOIN these two tables where the media_id is the same but the language must be a constant entered by me (es. "it") if available, otherwise it should use the default language saved in the watchlist table that will always be present.

The query I wrote is this :

SELECT * FROM watchlist
LEFT JOIN media 
   ON media.media_id = watchlist.media_id 
   AND (media.data_language = "it" OR media.data_language = watchlist.saved_language)

But using a simple OR I obviously get both languages as a result. I have already tried to use CASE too, but with the same result. What I want is to have back a single line for each media_id, where the language is preferably "it" or, only if it's not available in the media table, use the one saved in the watchlist table which will always have a match.

An example of the result I want:

media_id media_title saved_language data_language
000001 Title in English en en
000002 Title in Italian en it

Upvotes: 1

Views: 73

Answers (2)

Jens Krogsboell
Jens Krogsboell

Reputation: 1123

I would try

SELECT *
FROM media m
WHERE m.data_language = ‘it’
UNION
SELECT *
FROM media m
JOIN watchlist w ON w.media_id = m.media_id
AND w.saved_language = m.data_language
WHERE m.media_id NOT IN (
    SELECT m2.media_id
    FROM media m2
    WHERE m2.data_language = ‘it’
  )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use window functions:

SELECT w.*
FROM (SELECT wl.*, m.*,
             ROW_NUMBER() OVER (PARTITION BY wl.media_id
                                ORDER BY (CASE m.data_language WHEN 'it' THEN 1 ELSE 2 END)
                               ) as seqnum
      FROM watchlist wl LEFT JOIN
           media m
           ON m.media_id = wl.media_id AND
              m.data_language IN ('it', wl.saved_language)
     ) w
WHERE seqnum = 1;

However, if you just want the title, then two joins seems simpler:

select wl.*, coalesce(m_it.title, m_saved.title) as title
from watchlist wl left join
     media m_it
     on m_it.media_id = wl.media_id and
        m_it.language = 'it' left join
     media m_saved
     on m_saved.media_id = wl.media_id and
        m_saved.langauge = wl.saved_language;

Upvotes: 2

Related Questions