SQL Join one column twice

[EBrusse]

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

[Wendeley-North · Accepted solution]

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

[amitchandak]

@EBrusse , Create a new column in a

key = [Stockcode] & "-" & [Stockcode2]

new column in b

key = [Stockcode] & "-" & [Stockcode]

Join on these columns