How to find Parents and Children in a same table

Question

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

`

You need to do a LEFT JOIN for every relation...You might want to consider to drop the "Adjacency List" Model (parent id model) for a better model like "Nested Set" Model or "Closure Table" Model.. read http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Raymond Nijland, Jan 05 '18 at 13:43
@Mittal I need to list like parent->sub-parent->children if it is null, then start again parent->sub-parent->children — reegan29, Jan 05 '18 at 13:45
using UUID is pretty wastfull why you didn't use a AUTO_INCREMENT id? — Raymond Nijland, Jan 05 '18 at 13:45
@RaymondNijland I'm working in a Existing code and that id mapped with other tables. So I can't do anything with that id — reegan29, Jan 05 '18 at 13:47
@jarlh I just follow this question https://stackoverflow.com/questions/11720717/how-to-iterate-through-an-sql-table-which-parent-and-child-rows-are-in-same-t , I can remove that Limit — reegan29, Jan 05 '18 at 13:49
@RaymondNijland I just visited that side you gave me, That is awesome. I'm gonna try that. Thank for your help — reegan29, Jan 05 '18 at 13:51
Check [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — trincot, Jan 05 '18 at 14:23
Are you walking up (or down) a tree? What version are you using? Does it have CTEs? If not, upgrade. — Rick James, Jan 05 '18 at 17:45

Mittal Patel · Accepted Answer · 2018-01-05T14:20:22.537

3

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

edited Jan 05 '18 at 14:20

answered Jan 05 '18 at 13:39

Mittal Patel

2,732
14
23

It works dude, Can I append the Null categories too? I mean, the first one is SHAWL and it's null so I can list that first, then move to second(OFFER) if it is null we can show that also and move to next.. when the 'Electronics' comes, it starts to find that's sub-parents and children – reegan29 Jan 05 '18 at 14:07
USe the left join to allow the category which doesn't have any child... Please the updated answer – Mittal Patel Jan 05 '18 at 14:20
sorry for that miss understanding, If I can display all parent_id, I can just pass that in the code and get all related products. Thanks for your all replies. God Bless – reegan29 Jan 05 '18 at 15:11

How to find Parents and Children in a same table

1 Answers1