Full Outer Join (left join union right join) MySQL multiple tables

Question

I have looked at other related SO questions, but they couldn't click for me.

I have these tables: https://i.stack.imgur.com/cEYVn.png

In the end, I’d like to combine these 5 tables so everything from Location (maybe loc_type (int) replaced with loc_type_name), everything but loc_id from Hours (since it will be joined on loc_id), Same with Holiday, etc.

I am already aware of how to replicate this feature in MySQL:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

I got it working for any two tables, but couldn't go any further:

SELECT * FROM locations loc 
    LEFT OUTER JOIN location_types locType on locType.id = loc.loc_num 
UNION 
SELECT * FROM locations loc 
    RIGHT OUTER JOIN location_types locType on locType.id = loc.loc_num
UNION
SELECT * FROM locations loc 
    LEFT OUTER JOIN hours shours on loc.loc_num = shours.loc_id 
UNION
SELECT * FROM locations loc 
    RIGHT OUTER JOIN hours shours on loc.loc_num = shours.loc_id
UNION
SELECT * FROM locations loc 
    LEFT OUTER JOIN holiday hol on loc.loc_num = hol.loc_id 
UNION
SELECT * FROM locations loc 
    RIGHT OUTER JOIN holiday hol on loc.loc_num = hol.loc_id 

Any help will be appreciated it!

Note: There might be some additional columns for location and other tables, so in the end, I'd like a in which I'll hand pick the columns desired (i.e. FROM location select loc_id, loc_name, loc_phone)...

Answer 1

Just have a look at Below query, Hope this may help you! Later you can Modify Accordingly

This will join to all 5 tables. Here I kept Location as the Left Table so that All locations will be Displaying after firing this query and matching Id's will be matched to this table.

SELECT * FROM location LEFT JOIN location_type ON location_type.type_id=location.loc_id
    LEFT JOIN hours ON location.loc_id=hours.loc_id 
    LEFT JOIN holiday ON holiday.loc_id=location.loc_id 
    LEFT JOIN holiday_hours ON holiday.hol_id=holiday_hours.holiday_id