How to display name for C and X from a table category instead of using self join?

Question

1.00/5 (1 vote)

See more:

I work on SQL Server 2014 and I need to get categories c and x without using self join, but I don't know how to do that.

My data sample:

SQL

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')

Expected result:

categoryc	categoryx
bmw	mercedees
feat	toyota
mazda	suzoky

What I have tried:

SQL

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

Posted 31-Jan-22 23:11pm

ahmed_sa

Updated 1-Feb-22 0:45am

Sean Ewington

v2

Add a Solution

Comments

M Imran Ansari 1-Feb-22 5:41am

What you have tried is also giving the same/expected result which you show in your question.

_Asif_ 1-Feb-22 5:46am

Well this is not self join and secondly why do you think the approach is wrong?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-01-31T23:42:00

Solution 1

And?
If I run your code as shown, I get exactly what you say you expect:

bmw	mercedees
feat	toyota
mazda	suzoky

A JOIN is the right way to do it, and since you want to return data from 2 different rows on the same row, you need a JOIN for each column in the output.

Posted 31-Jan-22 23:42pm

OriginalGriff

M Imran Ansari · Answer 2 · 2022-01-31T23:55:00

The query you are using is not a Self join. Your query is also returning the same result as expected but that will return all categories and display name if exists in the detail table.
I have added another category:

INSERT INTO #category(categoryc,categoryx) VALUES (101,102)

Result:
categoryc categoryx
bmw mercedees
feat toyota
mazda suzoky
NULL NULL
If you are looking for eaxct match then use the folowing 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

Result:
categoryc categoryx
bmw mercedees
feat toyota
mazda suzoky