SQL Query Join 3 Tables with Same Structure

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?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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 DBACommented:
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.

SELECT
    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 SpecialistCommented:
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?
Jimbo99999Author Commented:
I finished my contract a week ago and I don't have access to the computer anymore.

Thanks for the help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.