MySQL How to JOIN rows from table using the results from multiple UNION

Question

Forgive me as I am very new to mysql. I have 4 tables that have information on Song, Remix, Release and Project, like the following:

Song song_id, song_year_released, project_id

Remix remix_id, remix_year_released, project_id

Release release_id, release_year, project_id

Project project_id, project_name

I would like to fetch all project_id's from multiple tables that are released in the year 1995. In the same query I would like to JOIN the Project table and get the project name for all of the project_id's from the results.

I have managed to get the project IDs using the below query with UNION:

SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION 
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION 
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995'

How would I go about joining the Project table in order to get the project_name for each of the project_id's from the above union? I have attempted the below code but of course it doesn't work.

SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION 
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION 
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995'
UNION 
SELECT p.project_name FROM `Project` p 
INNER JOIN `Project` p ON p.project_id = s.project_id 

Thank you in advance.

Answer 1

WITH mysql 8 you can do.

As mentioned in my comment MySQL has no FULL OUTER JOIN but you cqan simulate it

WITH CTE AS (SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION 
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION 
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995')
SELECT  p.*,CTE.* FROM  `Project` p LEFT JOIN CTE s ON p.project_id = s.project_id 
UNION 
SELECT  p.*,CTE.* FROM    CTE s LEFT JOIN `Project` p  ON p.project_id = s.project_id 

with earlier Version you need to replace the CTE with the hole SELECT

But this is always slow, of course depending on the database size

i see that you changed you query:

The logic of abouve query still works

WITH CTE AS (SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION 
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION 
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995')
SELECT  p.project_name  FROM  `Project` p INNER JOIN CTE s ON p.project_id = s.project_id 

in earlier Version of MySQL

you must Do following query

SELECT  p.project_name  FROM  `Project` p INNER JOIN (SELECT s.project_id FROM `Song` s WHERE song_year_released = '1995'
UNION 
SELECT r.project_id FROM `Remix` r WHERE remix_year_released = '1995'
UNION 
SELECT rl.project_id FROM `Release` rl WHERE release_year = '1995') s ON p.project_id = s.project_id