CODEWITHSUNDEEP
mysqlsql
php-b-grader
php-b-grader

Reputation: 3315

How to get all rows based on single id

I have a table with a parent/child relationship structure.

There are 3 types of entities:

  1. Actual Location (type=1)
  2. Sub Group (type=10)
  3. Head Group (type=100)

Example structure

id  parent  name                        type
28  0       Head Group                  100
10  28      --Location 1                1
12  28      --Location 2                1
16  28      --Location 3                1
17  28      --Location 4                1
19  28      --Location 5                1
29  28      --Location 6                1
8   28      -Sub Group 1                10
11  8       --Sub Group 1 Location 1    1
30  28      -Sub Group 2                10
13  30      --Sub Group 2 Location 1    1
14  30      --Sub Group 2 Location 2    1
15  30      --Sub Group 2 Location 3    1

I want to be able to select all rows if 1d 28 (Head Group) is passed in.

I've tried to create a statement but I think this is beyond me. ANy help would be appreciated...

SELECT CGroup.id, 
     CGroup.parent, 
     CGroup.name, 
     CGroup.type
FROM Customer AS CGroup 
INNER JOIN Customer AS CSubGroup
  ON CSubGroup.parent_id = CGroup.id
WHERE CGroup.parent_id=28

Upvotes: 0

Views: 46

Answers (1)

Abhi Beckert
Abhi Beckert

Reputation: 33369

You're making it too complicated. Keep it simple. Simple queries usually execute faster and have less bugs.

// first get all subgroups under the main group
subGroupIds = fetchField('
  select group_concat(id)
  from CGroup
  where parent = 28
    and type = 10
')

// then get everything
results = fetchRows('
  select *
  from CGroup
  where parent = 28
    or parent in (subGroupIds)
')

If you really want to do it "properly", I recommend implementing a Nested Set tree.

Upvotes: 1

Related Questions