Using the data from SQL Join to get another data from another table-CodePudding

I have a table that stores products and another table that stores prices for those products and another table that stores currencies used in prices table. And prices are connected via a column named "CARDREF" which basically points to the product row the price belongs to. And in that price row there is also a column named "CURRENCY" which points to a currency row. And in that currency row I can see the currency name and other data. I put snippets of those tables below.

Product Rows

Price Rows

Currency Rows

Basically I need it to selecs price rows that matches "CARDREF" on the prices table with "LOGICALREF" on the products table, then take the value in the column named "CURRENCY" on that prices row and use that value to retrieve the column named "CURNAME" in the currency table.

I got "CURRENCY" column vavlue using "JOIN" with products table and prices table but couldn't get "CURNAME" column value from currency table using the currency value i got from that join statement. Any help is appreciated.

CodePudding user response：

Using the demo data:

SELECT p.LogicalRef, p.Active, p.CardType, pr.CardRef, pr.ClientCode, pr.CLSPECode, pr.PayPlanRef, 
       pr.Price, pr.UOMRef, pr.INCVat, pr.Currency,  c.CurType, c.CurCode, c.CurName
  FROM @Products p
    LEFT OUTER JOIN @Prices pr
      ON p.LogicalRef = pr.CardRef
    LEFT OUTER JOIN @Currency c
      ON pr.Currency = c.CurType

Here we are using LEFT OUTER JOIN as we expect there may not be corresponding rows in the joined table (We know the demo data only has rows in @Prices for CardRef 1124). We then LEFT OUTER JOIN to @Currency on the defined key (CurType). The CurName column is now available in the result set.

LogicalRef  Active  CardType    CardRef ClientCode  CLSPECode   PayPlanRef  Price   UOMRef  INCVat  Currency    CurType CurCode CurName
---------------------------------------------------------------------------------------------------------------------------------------
...
1123        0       12          NULL    NULL        NULL        NULL        NULL    NULL    NULL    NULL        NULL    NULL    NULL
1124        0       1           1124                            0           123456  23      0       160         160     TL      Turk Lirasi
1124        0       1           1124                            0           123457  23      0       165         165     AWG     Aruba Florini