0

I would like to check if child rows exist 3 levels down, 2 levels down and 1 level down. 

select max(
 case 
  (select row from category where relatedRow in (select row from category where relatedRow=?)) is not null then 3
  (select row from category where relatedRow=?) is not null then 2
  (select row from category where row=?) is not null then 1
  else 0 end
) as level from category

How can I make this work?

This query goes as follows. If there are rows that have related rows of the related rows (grandchildren), level=3. If there are related rows (children), level 2. If there is a row=? (parent), level 1. If there is no parent (ghost), level 0.

Maciek Semik
  • 1,872
  • 23
  • 43

1 Answers1

0

You can use self join to get the expected value, e.g.:

select c1.row, c2.row, c3.row
from category c1 left join category c2 on c1.row = c2.related_row
left join category c3 on c2.row = c3.related_row
where c1.row = 1;

In short, to go to nth level, you need to self join the table n times.

Here is the SQL Fiddle.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • I understand. I am trying to get the level. I have a maximum of 3 levels, but I would like to use a "case" to return the level of children the parameter has. For your example, if row=1, it exists, it has children and grandchildren, therefore (level=3). But if row=4, it exists, it has children and NO grandchildren, therefore (level=2). This is what I need. A number representing the highest level of children. – Maciek Semik Mar 25 '16 at 23:34
  • In that case, you need to do something like this : http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql – Darshan Mehta Mar 26 '16 at 00:32