CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT clause with self joining table

Two tables:

//SKILL
SNAME
---------------------
C++
C#
C
JAVA

//REQUIRED
SNAME          REQUIRED
------------------------
C++               C
C#             reading
C              writing
C              reading
JAVA              C

I used this query

SELECT DISTINCT sq.SNAME,sq1.requires AS "requires" FROM SREQUIRED sq
INNER JOIN SREQUIRED sq1
ON sq.SNAME='C++';

but my output is 

SNAME                REQUIRES
-------------------------------
 C++                     C
 C++                  reading
 C++                  writing

my question is if i want to find the skill that directly required by the skills C++, what query should i use ? output should be like this:

SNAME                 REQUIRES
-------------------------------
 C++                     C
 C                    reading
 C                    writing

Because C++ required C , and C required reading and writing. it's just like first level second level and etc.

Upvotes: 1

Views: 169

Answers (3)

Roopesh
Roopesh

Reputation: 279

Use CTE to make it generic. Levels may go deeper than just 2. Use the following query to get required result.

with cte
As
(
Select SName, [Required] from courses where SName = 'C++'
Union All
Select courses.SName, courses.[Required] from courses 
   inner join cte on courses.SName = cte.[Required]
)
select * from cte

Hope it helps.

Upvotes: 1

G one
G one

Reputation: 2729

Not the best solution but you can try like this:

select * from srequired
where sname='C++'
UNION
select * from srequired
where sname in (select required from srequired where sname='C++')

Upvotes: 1

Richard Vivian
Richard Vivian

Reputation: 1750

The join criteria 

ON sq.SNAME='C++';

Does not relate the two tables (sq and sq1) , and limits the result set to sq.SNAME = 'C++', which is why you are gettig only SNAME = 'C++' in the output.

The join would need to be as follows.

ON sq.Requires = sq1.sName

Then in your Where clause you need to specify:

WHERE sq.sNAME = 'C++'

You will then have sq.sName with 'C++' in 2 lines. sq1.sName with 'c' in all columns. and sq1.Requires with 'Reading' and 'Writing'

To get the result set in a single output you will have to use a UNION or a CTE. a UNION will give you the 2 levels you need. a CTE can give you n-levels. i.e if reading had another per-requisite.

Upvotes: 0

Related Questions