CODEWITHSUNDEEP
mysqlsqlmariadb
reegan29
reegan29

Reputation: 930

How to find Parents and Children in a same table

I need to find parents and children in the below table. I tried with below query. But it throws the error

[Err] 1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

I need to find all parents until it reply null. Please help me to find that.

SELECT id, name 
FROM categories 
WHERE id IN (SELECT id FROM categories WHERE parentid IS NULL LIMIT 1)
UNION ALL
SELECT cat.id, cat.name
FROM categories as cat,
     categories 
WHERE categories.id = cat.parentid

enter image description here

`

Upvotes: 0

Views: 7742

Answers (1)

Mittal Patel
Mittal Patel

Reputation: 2762

You can achieve this by below query: This query display all the child with it's parent name. This results in multiple rows.

SELECT child.id
    ,child.NAME
    ,parent.NAME
FROM categories AS parent
LEFT JOIN categories AS child ON child.parentid = parent.Id

Order by can be this way, but didn't tested

SELECT child.id
    ,child.NAME
    ,parent.NAME
FROM categories AS parent
LEFT JOIN categories AS child ON child.parentid = parent.Id
ORDER BY coalesce(parent.id, child.id)
    ,parent.id IS NOT NULL
    ,child.id

Upvotes: 3

Related Questions