MySQL - getting only children of a specific parent
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 collect all children / grandchildren from the database. Now this query simply sorts the rows according to the parent-child rule and returns all rows. But I want to add a condition to the SQL so that it will only have children of a specific parent.
For example, I want to get all child rows / nodes / locations 19
. And the result should be as follows:
+ ------------- + ------------------ + ---------------- - + ----------------- + | 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 both of them return the same result as it does:
+ ------------- + ------------------ + ---------------- - + ----------------- + | location_id | location_name | location_parent | location_status | + ------------- + ------------------ + ---------------- - + ----------------- + | 22 | Dhaka District | 19 | publish | + ------------- + ------------------ + ---------------- - + ----------------- +
So what do I need to do to achieve the desired result?
source to share
This is actually an advanced database topic called recursion. The requirement is to get the data tree from the topmost parent and all its children, grand-child, etc.
Another DBMS provided a function to solve such a problem, for example. Microsoft SQL Server calls their solution CTE (Common Table Express), however this is a long time in mySQL and there is no easy solution to solve your problem. However, you can look up recursion in mysql for more details on this topic.
source to share
What you are looking for is called the "Closing" table. It is essentially a table for storing links to child, parent, and deep hierarchical data.
I found a great post here to help you achieve the results you want. What are the storage options for hierarchical data in a relational database?
source to share