CODEWITHSUNDEEP
sqloracle-databaseselect
Thapipo
Thapipo

Reputation: 311

Querying a hierarchical table

Let say we have this table SchoolClass

id_class    class       class_Name  id_class_req
--------    --------    ---------   --------
1          SQL9999          SQL     
2          PHP1111          PHP         1
3          JAV2222          Java        2

So a class can have an other class as requirement before you attempt this class. I wanna be able to sort every class that got a requirement and give the class like this : 

class       id_class_req
-------     -------------       
PHP1111     SQL9999
JAV222      PHP1111

this is my query. I'm only able give the id_class_req with this query. Is there a way to do this without subqueries or nested query.

SELECT SchoolClass.class, SchoolClass,id_class_req
FROM SchoolClass
WHERE SchoolClass.id_cours_prerequis Is Not Null;

Thanks you very much.

Upvotes: 1

Views: 68

Answers (4)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Oracle has a nice feature called hierarchical query that helps with that kind of table (i.e.: adjacency list). With that, you can write something like this:

SELECT CONNECT_BY_ROOT "class" as "Root class", "class" AS "Dep class"
FROM T
WHERE LEVEL = 2
CONNECT BY PRIOR "id_class_req" = "id_class";

See http://sqlfiddle.com/#!4/bbbc0/1

In such a query, Oracle will build a graph such as for each node, node.parent.id_class_req = node.id_class (CONNECT BY PRIOR "id_class_req" = "id_class"). After that, we keep only sub-graph of depth 2 (WHERE LEVEL = 2). Which is in fact what you are looking for.

As a picture worth 1000 words, given the above query and the sample data:

id_class    class       class_Name  id_class_req
--------    --------    ---------   --------
1          SQL9999          SQL     
2          PHP1111          PHP         1
3          JAV2222          Java        2
10         LIN101           Linux     
11         C101             C           10
12         SYSADMIN         SysAdmin    10
14         ELEC101          Electronics

Oracle will build that graph:

dot graph

And will only retain path of length 2 (i.e.: pair of nodes containing only a child and its direct parent):

ROOT CLASS  DEP CLASS
PHP1111     SQL9999
JAV2222     PHP1111
C101        LIN101
SYSADMIN    LIN101

Upvotes: 2

Ram
Ram

Reputation: 3089

Try a SELF JOIN

SELECT sc1.class, sc2.class 
FROM SchoolClass sc1
INNER JOIN SchoolClass sc2 ON sc1.id_class_req=sc2.id_class

Upvotes: 0

user1261620
user1261620

Reputation: 377

You could inner join against the same table

SELECT m.SchoolClass.class, m.SchoolClass,m.id_class_req, r.class as "Required Class"
FROM SchoolClass m, SchoolClass r
WHERE m.id_class_req = r.id_class    
m.SchoolClass.id_cours_prerequis Is Not Null;

Upvotes: 0

crthompson
crthompson

Reputation: 15865

Do this with a self join

SELECT s.class, rec.class
FROM SchoolClass s
INNER JOIN SchoolClass req on s.id_class=req.id_class_req
WHERE SchoolClass.id_cours_prerequis Is Not Null;

Upvotes: 0

Related Questions