CODEWITHSUNDEEP
sqlsql-server
user3919727
user3919727

Reputation: 343

SQL join with another table results in duplicate records

I'm trying to join two tables.

Table A:

RowID   Name        Active
4       Bangalore   1

Table B:

Sno  RowID  Type     Stores      Active
1    4      Legal    Center-A    1
2    4      Trade    Center-A01  1
3    5      Trade    Center-B    1

Here RowID is a foreign key for Table A. If the Type is Legal then LegalName should be Center_A and If the Type is Trade then TradeName should be Center_A01

This is what I tried and but I'm getting two rows one for Legal and other for Trade Type.

Name        Type      LegalName  TradeName   
Bangalore   Legal     Center-A   Center-A01
Bangalore   Trade     Center-A   Center-A01

What I really need is something like below

 Name        LegalName  TradeName   
 Bangalore   Center-A   Center-A01

Do I need to use CASE in this case? Any help would be greatly appreciated.

Upvotes: 0

Views: 44

Answers (1)

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

To achieve this you can make 2 joins, one for trade and second for legal.

select
     a.[Name]
    ,legal.[Stores] as LegalName
    ,trade.[Stores] as TradeName
from TableA a
join TableB trade
    on a.RowID = trade.RowID
    and trade.[Type] = 'Legal'
join TableB legal
    on a.RowID = legal.RowID
    and trade.[Type] = 'Trade'

Upvotes: 2

Related Questions