CODEWITHSUNDEEP
phpmysqlsqljoin
h404
h404

Reputation: 37

Parent / Child in same table

I have table structure like below:

id |parent|name |value
1  | 0    | aaa |
2  | 0    | bbb |
3  | 0    | ccc |
4  | 1    |     | 111
5  | 1    |     | 222
6  | 3    |     | 333

I want to display parent if it has child records.

Like:

(parent id + name + value first child)

1 - aaa - 111
3 - ccc - 333

Upvotes: 2

Views: 1031

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

There is no meaning of the first child in the database, you can get the first child by the mininum of the id or the minimum of the value, but the values are not stored with a specific order in the table, so you can't tell which value is the first one.

But, assuming that the id is auto incremental column, then value of the first child is the value of the minimum id, then you can do this:

SELECT
  t1.parent,
  t2.name,
  t1.value
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;

See it in action here:

This will give you :

| PARENT | NAME | VALUE |
-------------------------
|      1 |  aaa |   111 |
|      3 |  ccc |   333 |

Or: You can get it by the minimum value:

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

See it in action:

This will give you:

| PARENT | NAME | VALUE |
-------------------------
|      1 |  aaa |   111 |
|      3 |  ccc |   333 |

Upvotes: 1

Related Questions