CODEWITHSUNDEEP
mysqlparent-child
jad
jad

Reputation: 37

Mysql select only child from a table tree

I have a table name tbl_section this table is like that

db_secid   db_secname   db_secdesc    db_parent
1          Electrical                 NULL
2          Mechanical                 NULL
3          Plumbing                   NULL
4          Cables                      1
5          Lamps                       1
6          HDMI                        4

I want to select only the children of a row with db_parent=NULL. From the table above the result will be only Cables and Lamps.

I try with this query 

select 
s1.db_secname 
from tbl_section as s1
LEFT JOIN tbl_section as s2
ON 
s1.db_secid=s2.db_parent
WHERE s2.db_parent IS NULL

But this query give me a result I don't want this the result I have from this query

Mechanical,Plumbing,Lamps,HDMI

How can i solve this problem and have only the children who have a parent with db_parent = NULL?

Upvotes: 2

Views: 128

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

you could use inner join 

select t1.db_secname
from tbl_section as t1
inner join tbl_section as t2 
      on t2.db_parent is null and t2.db_secid = t1.db_parent

Upvotes: 2

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476624

Simply perform a JOIN, this will already filter out rows that have no parent. Furthermore you also swapped you parent-child relation: it should be s1.db_parent = s2.db_secid, not s1.db_secid=s2.db_parent:

SELECT s1.db_secname
FROM tbl_section AS s1
     JOIN tbl_section AS s2 ON s1.db_parent = s2.db_secid
WHERE s2.db_parent IS NULL

Upvotes: 2

Related Questions