CODEWITHSUNDEEP
sqljoinsql-server-2012
Nikhil
Nikhil

Reputation: 31

Create multiple joins to a lookup table from one table

Suppose I have a table A in sql where the data is given below

enter image description here

Now I have another Lookup table Table B

enter image description here

Now how do I get value of Type Name and Business Name for the same record from Lookup table B. For eg for a record where Type is T1 it should return Type Name as ' Product' and Business Name as 'SCI' for same record Request_ID = 1. I used inner join to do this but it conflicts since for same record in table A it tries to bring corresponding value from Lookup table. 

Select B.name, A.type,A.business_line 
from Table A A 
inner join Table B B on A.request_id = B.id

Upvotes: 0

Views: 1188

Answers (3)

Tanner
Tanner

Reputation: 22733

You just need to reference the lookup table twice with separate joins in order to retrieve 2 values. The below sample shows you how you can do that and there is a runnable demo on the link:

-- setup demo schema with data
create table TableA 
(
  request_id int,
  [type] nvarchar(10),
  [business_line] nvarchar(10)  
);

create table TableB 
(
  id int,
  [code]  nvarchar(10),
  [name]  nvarchar(10)
);

INSERT INTO TableA
    ([request_id], [type], [business_line])
VALUES
    (1, 'T1', 'BL1'),
    (2, 'T1', 'BL2'),
    (3, 'T2', 'BL3'),
    (4, 'T1', 'BL1')
;

INSERT INTO TableB
    (id, [code], [name])
VALUES
    (19, 'BL1', 'SCI'),
    (20, 'BL2', 'PCI'),
    (67, 'T1', 'Product'),
    (68, 'T2', 'Substance')
;

Query

select A.request_id, A.[type], A.[business_line], 
        Btype.[name] as Type_Name, 
        BName.[name] as Business_Name
from TableA A
inner join TableB BType on A.[type] = BType.[code]
inner join TableB BName on A.[business_line] = BName.[code];

Produces: (Note row 3 doesn't have matching lookup in your sample data

| request_id | type | business_line | Type_Name | Business_Name |
| ---------- | ---- | ------------- | --------- | ------------- |
| 1          | T1   | BL1           | Product   | SCI           |
| 2          | T1   | BL2           | Product   | PCI           |
| 4          | T1   | BL1           | Product   | SCI           |

You could modify the query joins if you had rows without lookup values, so that rows aren't excluded like row 3..

View on DB Fiddle

Upvotes: -1

Nick
Nick

Reputation: 147166

You need to JOIN table A to table B twice, once to get the Type Name and once to get the Business Name:

SELECT a.request_id, a.type, b1.name AS [Type Name], a.business_line, b2.name AS [Business Name]
FROM TableA a
JOIN TableB b1 ON b1.code = a.type
JOIN TableB b2 ON b2.code = a.business_line

Demo on dbfiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Do you just want two joins?

select r.name as producct, l.name as businessname,
       a.type, a.business_line
from a join
     lookup r
     on a.request_id = r.id join
     lookup l
     on a.business_line = l.id;

Upvotes: 0

Related Questions