Forum Discussion
SQL Join one column twice
Hello everyone!
My question is aimed at creating SQL code.
I have a table which contains 2 columns with different stockcodes for one product.
The description for this stockcode is stored in a different table.
I would like to create a dataset which displays the [Stockcode], the [DescriptionBasedOnCol1] and the [DescriptionBasedOnCol2].
This requires an SQL like:
SELECT
a.Stockcode ,
b.DescriptionBasedOnCol1 ,
b.DescriptionBasedOnCol2 ,
FROM
a
LEFT OUTER JOIN b ON a.Stockcode = b.Stockcode
LEFT OUTER JOIN b ON a.Stockcode2 = b.Stockcode
Obviously this does not work. I want to know how to set this up!
Thanks in advance!
Erwin
Your description isn't exactly clear, but assuming you have Table 1 with columns [ProductName], [StockCode1], [StockCode2] and Table 2 with [StockCodeX], [Description]:
SELECT a.ProductName, a.StockCode1, b.Description AS Description1, c.Description AS Description2
FROM Table1 a
LEFT JOIN Table2 b ON a.StockCode1 = b.StockCodeX
LEFT JOIN Table2 c ON a.StockCode2 = c.StockCodeX
2 Replies
Anonymous , Create a new column in a
key = [Stockcode] & "-" & [Stockcode2]
new column in b
key = [Stockcode] & "-" & [Stockcode]
Join on these columns
Your description isn't exactly clear, but assuming you have Table 1 with columns [ProductName], [StockCode1], [StockCode2] and Table 2 with [StockCodeX], [Description]:
SELECT a.ProductName, a.StockCode1, b.Description AS Description1, c.Description AS Description2
FROM Table1 a
LEFT JOIN Table2 b ON a.StockCode1 = b.StockCodeX
LEFT JOIN Table2 c ON a.StockCode2 = c.StockCodeX
