i work on sql server 2014 i need to get categories c and x without using self join
but i don't know how to make that
my data sample
create table #category
(
categoryc int,
categoryx int
)
insert into #category(categoryc,categoryx)
values
(19,20),
(50,75),
(80,70)
create table #categorydetails
(
categoryid int,
categoryname nvarchar(300)
)
insert into #categorydetails(categoryid,categoryname)
values
(19,'bmw'),
(20,'mercedees'),
(50,'feat'),
(75,'toyota'),
(80,'mazda'),
(70,'suzoky')
select d1.categoryname as categoryc,d2.categoryname as categoryx from #category c
left join #categorydetails d1 on d1.categoryid=c.categoryc
left join #categorydetails d2 on d2.categoryid=c.categoryx
so how to get data above without using self join
are there are another way to do that without using self join
expected result
| categoryc | categoryx |
| bmw | mercedees |
| feat | toyota |
| mazda | suzoky |
Muhammad Imran AnsariPosted Feb 1, 2022, 10:45 AM
The query you are using is not a Self join. Your query also returns the same result but that will return all categories and name if exists in the detail table. If you are looking for exact match then use the following query:
SELECT CDC.categoryname AS categoryc, CDX.categoryname AS categoryx
FROM #category c
INNER JOIN #categorydetails CDC ON c.categoryc = CDC.categoryid
INNER JOIN #categorydetails CDX ON c.categoryx = CDX.categoryid