CODEWITHSUNDEEP
mysqlsql
Omar Faruk Sharif
Omar Faruk Sharif

Reputation: 69

MySQL - Getting children of a specific parent only

If I run the following query:

SELECT loc2.* FROM `locations` AS loc
INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent
WHERE loc.location_status='publish'

I get the following result:

+-------------+------------------+-----------------+-----------------+
| location_id | location_name    | location_parent | location_status |
+-------------+------------------+-----------------+-----------------+
|          19 | Dhaka Division   |             564 | publish         |
|          22 | Dhaka District   |              19 | publish         |
|          26 | Dhaka City       |              22 | publish         |
|          28 | Mirpur           |              26 | publish         |
|          30 | Mirpur - 12      |              28 | publish         |
|          32 | Mirpur DOHS      |              30 | publish         |
|         634 | Gazipur District |              19 | publish         |
+-------------+------------------+-----------------+-----------------+
7 rows in set (0.00 sec)

Actually I am trying to get all the children/grand-children from database. Now the above query is just sorting the rows as per the parent-child rule and returning all the rows. But, I want to add a condition to the SQL, so that it gets children only of a specific parent.

For example, I want to get all the child rows/nodes/locations of 19. And the result set should be following:

+-------------+------------------+-----------------+-----------------+
| location_id | location_name    | location_parent | location_status |
+-------------+------------------+-----------------+-----------------+
|          22 | Dhaka District   |              19 | publish         |
|          26 | Dhaka City       |              22 | publish         |
|          28 | Mirpur           |              26 | publish         |
|          30 | Mirpur - 12      |              28 | publish         |
|          32 | Mirpur DOHS      |              30 | publish         |
+-------------+------------------+-----------------+-----------------+

I tried with this:

SELECT loc2.* FROM `locations` AS loc
INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent
WHERE loc.location_status='publish' AND loc2.location_parent=19

and this:

SELECT loc2.* FROM `locations` AS loc
INNER JOIN `locations` AS loc2 ON loc.location_id = loc2.location_parent
WHERE loc.location_status='publish' AND loc.location_parent=19

But they both return the same result, as it should :

+-------------+------------------+-----------------+-----------------+
| location_id | location_name    | location_parent | location_status |
+-------------+------------------+-----------------+-----------------+
|          22 | Dhaka District   |              19 | publish         |
+-------------+------------------+-----------------+-----------------+

So, what should I do to achieve the result I need?

Upvotes: 3

Views: 126

Answers (2)

J A
J A

Reputation: 1766

What you are looking for is called "Closure" table. This is essentially a table for keeping the references of child, parent and depth of hierarchical data.

I found an excellent post here which will help you achieve desired results. What are the options for storing hierarchical data in a relational database?

Upvotes: 0

Mubashar
Mubashar

Reputation: 12658

Actually this is an advanced topic of databases called recursion your requirement is to get the tree of data from the top most parent and all its child, grand-child and so on.

Other DBMS has provided feature to resolve such problem e.g. Microsoft SQL Server calls its solution CTE (Common Table Express), however this is long due in mySQL and there is no simple solution to resolve your problem. However you can search recursion in mysql to get more details on this topic.

Upvotes: 0

Related Questions