CODEWITHSUNDEEP
sqlsql-serverdatabasejoin
Muthukumar Palaniappan
Muthukumar Palaniappan

Reputation: 1670

SQL Server Inner Join on same column with two tables

I am thinking about a possibility in doing inner joins with the below 3 tables.

CategorizedItems

CategoryId | linkid | type 
------------------------------
1              1        a1  
1              2        a1  
1              1        a2

A1_Items

itemid  | Name | A1 Fields
-----------------------------
1          N1
2          N2

A2_items

itemid  | Name | A2 Fields
-----------------------------
1          N3

CategorizedItems.linkId can hold A1_items.itemid and A2_items.itemid. Because of that, there are no primary key - foreign key relationship among them.

I am in the need to get the names of the items under CategoryId 1.

Is there a possibility to do this. I dont have any field representing 'type' in A1_items and A2_Items.

I guess that I can achieve it by doing 2 queries (or) By doing a union with the results of 2 separate queries.

In my real time scenario, I have multiple tables connected with these 3 tables. Redoing the joins for all the other tables, just for the above scenario is costly..

Any help on this would be much appreciated.

Upvotes: 0

Views: 7481

Answers (3)

Dan
Dan

Reputation: 10700

Just use left joins and apply a suitable filter to your joins like this:

SELECT CategoryId, COALESCE(A1_Items.Name, A2_Items.Name) AS Name
FROM CategorizedItems
LEFT JOIN A1_Items ON A1_Items.itemid = CategorizedItems.linkid AND CategorizedItems.type = 'a1'
LEFT JOIN A2_Items ON A2_Items.itemid = CategorizedItems.linkid AND CategorizedItems.type = 'a2'

Fiddle Demo

Upvotes: 3

Nitu Bansal
Nitu Bansal

Reputation: 3856

try this one

 SELECT CategoryId, isnull(A1_Items.Name, A2_Items.Name) AS Name FROM CategorizedItems LEFT JOIN A1_Items ON A1_Items.itemid = CategorizedItems.linkid AND CategorizedItems.type = 'a1' LEFT JOIN A2_Items ON A2_Items.itemid = CategorizedItems.linkid AND CategorizedItems.type ='a2'

Upvotes: 1

alzaimar
alzaimar

Reputation: 4622

Another approach:

select CategoryID, Name
from CategorizedItems c
     join (select 'a1' as table, ItemId, Name from A1_Items
           union
           select 'a2' as table, ItemId, Name from A2_Items) a
     on c.linkId = a.ItemId and c.type = a.table

Upvotes: 2

Related Questions