Join Assistance Needed on Three Tables

[Auguy]

I have 3 tables, an order table with product codes, a customer template table with product codes, and a product table. I want to select the product code and some other data from the order table (assume I now the exact order #) and left outer join the template table on the product code to get all of the product codes that have been ordered or could be ordered. I also want to get some of the product code table columns with a join. I was thinking of using a CTE for the join of the order and product table, then join that to the product table. Pseudo code something like

With CTE
Select ProductCode
From OrderTable
Left Outer Join TemplateTable on ProductCode
...
Select CTE.*, ProductTable.Desciption,...
From CTE
Inner Join ProductTable on ProductCode

Is there a better way to do this? I don't see how to do this in one join. Or should I start with the Product table?

Auguy
Sylvania/Toledo Ohio

 

[Olaf Doschke]

If you have three tables you need two joins, even if the joins are by the same field, but you don't need a CTE.

Also, your join condition is only sketched here, I assume, you know you have to have Ordertable.ProductCode = ProductTable.Productcode for one join and Ordertable.ProductCode = TemplateTable.ProductCode for the other join.

Bye, Olaf.

 

[Auguy]

Thanks Olaf. I will see if I can do it without the CTE. On the join, yeah I was trying to save some typing on my code.

Auguy
Sylvania/Toledo Ohio