CODEWITHSUNDEEP
mysqlsql
Jibeji
Jibeji

Reputation: 473

Display child and parent relationship (if any) in a same table

I have this table

| id |parent|name|
| 1  | NULL |  E |
| 2  | NULL |  B |
| 3  | 5    |  U |
| 4  | 5    |  X |
| 5  | NULL |  C |
| 6  | NULL |  A |

I would like the list, ordered by parent's name, of all ID whether they have a parent or not:

| id |parent|name|has_child|
| 6  | NULL |  A |    0    |
| 2  | NULL |  B |    0    |
| 5  | NULL |  C |    1    |
| 3  | 5    |  U |    0    |
| 4  | 5    |  X |    0    |
| 1  | NULL |  E |    0    |

Is it possible?

I have tried many things but never get the proper answer, and I don't really know how to add the 'has_child' column

SELECT
  t1.parent,
  t2.name
FROM tablename AS t1
INNER JOIN
(
  SELECT MIN(id) AS id, parent
  FROM tablename
  GROUP BY parent
) AS t22 ON t22.id = t1.id AND t1.parent = t22.parent
INNER JOIN tablename AS t2 ON t1.parent = t2.id;

Upvotes: 3

Views: 2675

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You can use a self join -- because you want the name of the parent and not the id -- and coalesce() for ordering:

select t.*,
       (case when exists (select 1 from t tc where tc.parent = t.id)
             then 1 else 0
        end)
from t left join
     t tp
     on t.parent = tp.id
order by coalesce(tp.name, t.name),   -- group rows by the parent, if any
         (tp.name is null) desc,      -- put parent first
         t.name;                      -- order by children

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

I would use a self join here:

SELECT DISTINCT
    t1.id,
    t1.parent,
    t1.name,
    1 - ISNULL(t2.id) has_child
FROM tablename t1
LEFT JOIN tablename t2
    ON t1.id = t2.parent
ORDER BY
    t1.id;

The join condition used here, which matches a given record as a parent to one or more children, is that the current id is also the parent of some other record(s). Note that we need SELECT DISTINCT here, because a given parent might match to more than one child record.

Upvotes: 4

JoPapou13
JoPapou13

Reputation: 773

I hope that you find this answer a little bit useful. The subquery gets the distinct id of parents and excludes the blanked fills.

SELECT *,
      CASE WHEN id IN (SELECT DISTINCT parent
                        FROM tablename
                        WHERE parent IS NOT NULL)
            THEN '1' ELSE '0'
            END AS has_child
FROM tablename 
ORDER BY name;

Upvotes: 2

Akina
Akina

Reputation: 42622

SELECT t1.id, t1.parent, t1.name, MAX(t2.parent is not null) has_child
FROM table t1
LEFT JOIN table t2 ON t1.id = t2.parent_id
GROUP BY t1.id, t1.parent, t1.name

Upvotes: 1

Related Questions