3

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?

Omar Faruk Sharif
  • 69
  • 4
  • MySQL has no support for hierarchical/recursive queries. You need to do what you want using a stored procedure, rather than a `select` statement -- or change your data structure so you have the entire path as a column. – Gordon Linoff Dec 22 '14 at 12:51
  • ...or join the table to itself as often as could possibly be required (yuk), or handle the recursion within application level code (e.g. PHP), or use a nested set (as opposed to the so-called 'edge-' or 'adjacency list' shown, or the materialized path suggested by GL) – Strawberry Dec 22 '14 at 12:52
  • ...or switch to a different DBMS. – Alexander Dec 22 '14 at 13:15

2 Answers2

0

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.

Mubashar
  • 12,300
  • 11
  • 66
  • 95
0

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?

Community
  • 1
  • 1
J A
  • 1,776
  • 1
  • 12
  • 13