We help IT Professionals succeed at work.

SQL Query Join 3 Tables with Same Structure

Jimbo99999 asked
Last Modified: 2018-12-12
Good Day Experts!

I have a SQL issue that I am in need of help with.  I have 3 temp tables that have the same structure.  I need to join all 3 together. However, all 3 temp tables may or may not have matching records.  Temp table 1 may be the same as Temp table 2 and 3. Temp table 1 may not have a match in 2 or 3.  It could have a match in 2 but not 3. Essentially, any combination from the 3 tables is possible.  

Thinking out loud, if I left join Temp table 2 to Temp table 1 then join Temp table 3, would I then miss out on the records that may or may not have matched between 1 and 3?

Is this possible?

Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Join or combine?  IF you created three temp tables that really cannot be joined, I would guess you have a design problem.

If you want the results combined, I would look at a UNION or UNION ALL.

If you need some columns from each of the tables like you've joined them, look at a UNION and a PIVOT.

If you want anything more specific, tell us the database type and version then provide sample data and expected results.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

You can use FULL OUTER JOINs, which is basically a LEFT JOIN and a RIGHT JOIN combined.  The thing to keep in mind is that you have to use COALESCE on all the key/join columns, because you don't know for sure which table(s) will have a given key value and thus return values for that key.

    COALESCE(t1.key_col, t2.key_col, t3.key_col) AS key_col,
    t1.data_col1 AS t1_data_col1, /*will be NULL if not found for that key*/
    t2.data_col1 AS t2_data_col1, /*will be NULL if not found for that key*/
    t3.data_col1 AS t3_data_col1 /*will be NULL if not found for that key*/
FROM dbo.table1 t1
FULL OUTER JOIN dbo.table2 t2 ON t2.key_col = t1.key_col
FULL OUTER JOIN dbo.table3 t3 ON t3.key_col IN (t1.key_col, t2.key_col)
awking00Information Technology Specialist

What is it that you're looking for? Is it just a set of the unique records among the three tables or do you want to know all of the combinations (1=2=3, 1=2 but not 3, 1=3 but not 2, 2 = 3 but not 1, or 1,2,and 3 are unique? Could you provide some sample data for the three tables and what you expect for a result?
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)