Joining with a different (foreign keys) while using the same key on a different table

Question

I'm a bit new to SQL and I'm still learning how to efficiently use joins. I have two tables.

1. contains Materials and Alternate Materials
2. contains Real_ID and Materials.

The code below works however I feel like this could be done more efficiently. I've been told I can do this in one join using CTE. I'm not sure how I would go about doing that. Any ideas?

SELECT a.MATERIAL_Num, a.ALT_MATERIAL_Num, b.Real_ID, c.Real_ID
FROM View.MaterialTable as a
LEFT JOIN(SELECT Real_ID, Material FROM View.Real_Item_ID) as b
ON a.Material_Num = b.Material
LEFT JOIN(SELECT Real_ID, Material FROM View.Real_Item_ID) as c
ON a.ALT_MATERIAL_Num = c.Material

• Jesse C. Slicer: You might want to change your title - you are using a different (foreign key) column for the second join - it's just the same key on the target table.
• Sᴀᴍ Onᴇᴌᴀ: It is fine to leave this question as is, since it already has an answer, however before asking another question with the sql tag please read the sql tag wiki - especially the Question Guidelines section.

Answer 1

If you are new to SQL I can give you one piece of advice - a lot of the advice you get will be bad advice!

In this case, it is doubtful that you will get advantage from "using CTEs" - that is something that gets thrown around as if it were a magical fix for bad query performance.

In general, your query is fine.

Three points:

1. You don't need to use expressions like LEFT JOIN(SELECT Real_ID, Material FROM View.Real_Item_ID) as b in this query. Just tell SQL the tables you want to join and and let it build the query. There is no reason for the "premature selection" as SQL will be able to resolve selecting the needed columns after the joins are all resolved.

2. SQL is about DATA. You have to provide context for your queries in order to make good judgments. In this case, we don't know what the primary keys are in these tables. Is Real_ID supposed to always match either Material_Num or Alt_Material_Num? Is Material_Num, or Alt_Material_Num, or Real_ID ever null? Provide some sample data that covers all the possible cases for how these three IDs work together and explain the reason for the query. If all this information is missing or unknown, you have to write more conservatively (like assuming nulls are possible, duplicates are possible, missing data is possible ... when perhaps these things are not actually possible at all).

3. Everything must be tested. You can verify if a CTE works better or not by testing the result. There are three tables here. I don't see how a CTE can magically do this work with one join (because you need two joins to join three tables). Personally I don't see any obvious use of CTE's here that makes sense (well, this is three points, I guess).

Here's how I would write your query:

select 
 a.Material_Num,
 a.Alt_Material_Num,
 b.Real_ID as Real_ID_From_Material_Num,
 c.Real_ID as Real_ID_From_Alt_Material_Num
from 
 #MaterialTable as a
 left join #Real_Item_ID as b
 on a.Material_Num = b.Material
 left join #Real_Item_ID as c
 on a.Alt_Material_Num = c.Material;