CODEWITHSUNDEEP
sql-server
Batuta
Batuta

Reputation: 1714

SQL Select To Reference A Lookup Based on Two Columns

I am having quite a hard time getting to build the correct SQL statement for this.

Sample Data (Simplistic View):

MasterTable
Product    DebitCode   CreditCode
Camera         1           4
Computer       2           5
Cellphone      3           6


LookupTable
Code    Description
 1      Debit Camera 
 2      Debit Computer 
 3      Debit Cellphone
 4      Credit Camera
 5      Credit Computer
 6      Credit Cellphone

I have no problem making an inner join for the DebitCode with the Code in the Lookup table (or the CreditCode) but when I try to use both, I get two entries for each.

I need my data to be retrieved such that the description for both the Debit Code and the Credit Code is all in one row.

Thanks.

Upvotes: 1

Views: 2713

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

You'll want two separate joins to your LookupTable, one for each code.

select mt.Product, 
       lt1.Description as DebitDescription, 
       lt2.Description as CreditDescription
    from MasterTable mt
        inner join LookupTable lt1
            on mt.DebitCode = lt1.Code
        inner join LookupTable lt2
            on mt.CreditCode = lt2.Code

Upvotes: 3

Related Questions