Comparing data in three tables

Looking to compare data from three different data sources, I have far more columns to include in the comparison, but for simplicity

Lets just say I have:

Table_A                  Table_B                  Table_C
ID   Somevalue           ID    Somevalue          ID     Somevalue
1          3              1          3             2            5 
2          4              3          5             3            6

Open in new window

I would like the query results as:

ID   Value_A    Value_B    Value_C
1          3          3       NULL
2          4       NULL          5
3       NULL          5          6

Open in new window

This obviously involves at least one Full Outer Join

SELECT Coalesce(A.ID, B.ID) as ID, A.SomeValue as Value_A, B.SomeValue as Value_B
FROM SELECT ID, SomeValue FROM Table_A as A
SELECT ID, SomeValue from Table_B as B

But I'm confused on how to accomplish this for Table_C.  Do I have to wrap the above as a subquery or CTE and then join that to Table_C, or can I simply add another line for Table_C (and if the latter) do I join on A.ID = C.ID or B.ID = C.ID or some other join condition?

Or would some sort of union query as a subquery and a Pivot query work better
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
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.

why don't you use direct table join instead of join of subqueries?

Coalesce(A.ID, B.ID) as ID, A.SomeValue as Value_A, B.SomeValue as Value_B, C.SomeValue as Value_c
from a full outer join b on =
left outer join c on = or =

Open in new window

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
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
That might work, I'll give it a try.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
That worked perfectly.
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
Microsoft SQL Server

From novice to tech pro — start learning today.