CODEWITHSUNDEEP
sqlpostgresqljoin
Curious Student
Curious Student

Reputation: 156

What kind of join to use on SQL tables

I have 3 tables that contain data on movies. They are called appalling, average and outstanding. They all have columns title_types, genre, year and rating. I am trying to join all 3 tables into a new table called facts, using this hint:

Think carefully about the join types for this query to avoid losing records from the individual tables.

My initial thought is to join all tables using a full outer join, and doing some digging, this was my code

CREATE TABLE Facts as 
SELECT Appalling.title_type, Average.title_type, Outstanding.title_type
FROM Appalling 
FULL OUTER JOIN Average ON Appalling.title_type = Average.title_type
FULL OUTER JOIN Outstanding ON Appalling.title_type = Outstanding.title_type;

However I am not sure if this is the smartest way to join all tables. Any help is appreciated. I believe the final output should look like such:

title_type | genre  | year | appalling | average | outstanding
movie        drama    1992    1            NULL      NULL
tv           comedy   2001    NULL          15       NULL
short        drama    2014    NULL         NULL      17

Upvotes: 0

Views: 50

Answers (1)

Magisch
Magisch

Reputation: 7352

This seems like extremely bad data schema design.

You should be using 1 table with simply a column that indicates if a movie is average, apalling or outstanding.

But, failing that and if the other columns are similar in both tables, you can use UNION ALL: (Updated so you can still distinguish.

CREATE TABLE Facts -- Data Types are for demo purposes, I have no idea what you use, use the appropriate data type yourself
( 
column_types varchar(500), 
title varchar(500), 
genre varchar(500), 
year int, 
rating int,
ratingType varchar(500)
);

INSERT INTO Facts (column_types, title, genre, year, rating, ratingType)
(
SELECT a.column_types, a.title, a.genre, a.year, a.rating, 'Average' AS ratingType FROM Average AS a
UNION ALL
SELECT ap.column_types, ap.title, ap.genre, ap.year, ap.rating, 'Appalling' AS ratingType FROM Appalling AS ap
UNION ALL
SELECT o.column_types, o.title, o.genre, o.year, o.rating, 'Outstanding' AS ratingType FROM Outstanding AS o
);

Upvotes: 2

Related Questions