Reputation: 343
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
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