CODEWITHSUNDEEP
sqldatabasepostgresqljoinleft-join
Jonathan
Jonathan

Reputation: 1286

How do I join two tables to a first and then combine a column?

Let's say I have three tables in a PostgreSQL database. A films table

+-------------+----------+
|    film     | theme_id |
+-------------+----------+
| film name 1 |      23  |
| film name 2 |      56  |
| film name 3 |      11  |
| film name 4 |      12  |
+-------------+----------+

a themes_old table

+----------+------------+
| theme_id | streams_on |
+----------+------------+
|       23 | Spotify    |
|       56 | Tidal      |
+----------+------------+

and a themes_new table

+----------+------------+
| theme_id | streams_on |
+----------+------------+
|       11 | Spotify    |
|       56 | Tidal      |
+----------+------------+

I want to join both the second and third tables to the first but without the repeated streams_on column. E.g. if I run 

SELECT * 
FROM films as f 
LEFT JOIN themes_old as to ON f.theme_id=to.theme_id 
LEFT JOIN themes_new as tn on f.theme_id=tn.theme_id

I would get 

+-------------+----------+------------+------------+
|    film     | theme_id | streams_on | streams_on |
+-------------+----------+------------+------------+
| film name 1 |       23 | Spotify    | [null]     |
| film name 2 |       56 | Tidal      | Tidal      |
| film name 3 |       11 | [null]     | Spotify    |
| film name 4 |       12 | [null]     | [null]     |
+-------------+----------+------------+------------+

But I want to merge the last two columns so that there is just one streams_on column such that for any row if one streams_on column value is null, take the other one, if both are not null take perhaps the first one and if both are null then default to null. This should give a table like this:

+-------------+----------+------------+
|    film     | theme_id | streams_on |
+-------------+----------+------------+
| film name 1 |       23 | Spotify    |
| film name 2 |       56 | Tidal      |
| film name 3 |       11 | Spotify    |
| film name 4 |       12 | [null]     |
+-------------+----------+------------+

I feel like this is a job for self join or something but I can't find much by searching. Any ideas? By the way both streams_on columns should be considered enums.

Upvotes: 0

Views: 52

Answers (3)

Babu Balakrishnan
Babu Balakrishnan

Reputation: 31

This can be done using isnull function. I used SQLserver for this solution. It checks the theme available in the new table if not it uses the old theme. Let me know if this helps you. 

select  f.film_name, 
    isnull(new.theme_id,old.theme_id), 
    isnull(new.streams_on, old.streams_on) 
    from films f 
            left join themes_old old 
                on f.theme_id = old.theme_id 
            left join themes_new new 
                on f.theme_id = new.theme_id

Upvotes: 0

JHarper
JHarper

Reputation: 11

I'd suggest using a Case Statement if only because you can add in a value to appear in place of the null value. It makes it easier to quickly export the information to reports

SELECT 
theme_id,
CASE
WHEN themes_new.streams_on IS NOT NULL THEN themes_new.streams_on
WHEN (themes_new.streams_on IS NULL AND themes_old.streams_on IS NULL) THEN "None"
ELSE themes_old.streams_on

FROM films as f
LEFT JOIN themes_old as to on f.theme_id=to.theme_id 
LEFT JOIN themes_new as tn on f.theme_id=tn.theme_id

Upvotes: 0

Nick
Nick

Reputation: 147196

You can use COALESCE to merge the two column values:

SELECT f.film, f.theme_id, COALESCE(to.streams_on, tn.streams_on) AS streams_on
FROM films as f 
LEFT JOIN themes_old as to on f.theme_id=to.theme_id 
LEFT JOIN themes_new as tn on f.theme_id=tn.theme_id

If you want to prioritise the "new" streams over the "old", just change the order of the values in the COALESCE i.e.

COALESCE(tn.streams_on, to.streams_on) AS streams_on

Upvotes: 2

Related Questions