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

sql merge two tables with different fields
how to combine two select queries in sql with different columns
sql merge two tables with same columns
excel merge two tables based on one column
sql join multiple tables with conditions
how to retrieve data from two tables with one sql statement
inner join
sql select from multiple tables without join

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.

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

Excel: Merge tables by matching column data or headers, See how to quickly merge two tables in Excel by matching data in one or more If you have an Excel 365 subscription, then you can use a more table with order IDs in the first column and you wish to copy those IDs to the� You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows. If the rows in both tables match up, you can merge the columns of one table with another—by pasting them in the first empty cells to the right of the table.

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

Combine results from several SQL tables, How do you combine results from more than one SQL table? JOIN – You can use joins to combine columns from one or more queries into one result. If you join two tables, one containing 5 row, and the other 10, the result may contain anywhere from 0 to 50 First, each query must have the same number of columns. Make sure the workbook contains at least two tables, and that each table has a column that can be mapped to a column in another table. Do one of the following: Format the data as a table , or Import external data as a table in a new worksheet.

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

A SQL join on multiple tables: overview and implementation, Joins are used to combine the rows from multiple tables using mutual columns. Noting that joins can be applied over more than two tables. To apply join between two tables, one table must contain a column that is a reference As shown in the query above, first we need to specify the columns we want to� First, click to place your insertion point in the cell where you would like your table split to begin. The cell that contains the insertion point will become the top row of the second table. Head to Table Tools > Layout, and then click the “Split Table” button. Your table is now split into two tables.

Learn SQL: Join multiple tables, To achieve that, we'll combine INNER JOINs and LEFT JOINs. The first example we'll analyze is how to retrieve data from multiple tables using only INNER JOINs. Then we join these 4 rows to the next table (country), and again we have Multiple options to transposing rows into columns � How to copy� This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. Cross JOIN Syntax is, SELECT column-name-list from table-name1 CROSS JOIN table-name2;

Combine Information from Multiple MySQL Tables with JOIN , You can use a JOIN SELECT query to combine information from more than one Tables are combined by matching data in a column — the column that they have It has the same first two rows as the inner join, but it has two additional rows� By default, .join() will attempt to do a left join on indices. If you want to join on columns like you would with merge(), then you’ll need to set the columns as indices. Like merge(), .join() has a few parameters that give you more flexibility in your joins.

Combining Data with Joins, When we join these two tables, we will merge their columns together into one new table. The first join type we'll consider is the inner join. This is done with a JOIN.. NOTE: I'll assume your table are named product and unit; please replace those with the actual names if you try to use any of this.. If every row in product has a valid weight or dimension unit of measure ID- that is to say, if none of the IDs is NULL, and all ID values actually exist in unit - then you can do this with an INNER JOIN.

Comments
  • use coalesce(to.streams_on, tn.streams_on) as streams_on
  • I have sample, but not in postgressql, in sql server. I used isnull(...) with left outer join