0

There is a table

EDIT: The previous table was not clear because of the id and field_id being the same That is the reason why the solution below given by you guys didnt work.

The parent_id which is stored is not the field_id but the ID generated automatically.

So in this case, all the 3 columns have to be taken into consideration.

In the other cases, the field_id is actually used to construct the sql.

But in this case, the id of the table is stored as the parent_id and not the field_id.

-------------------------
id | field_id | parent_id |
---------------------------
1     10           0
2     11           1
3     12           1
4     13           2
5     14           2
6     15           2
7     16           2
8     17           4

I m having this table in which the field_id and parent_ids are present. I need to get all the field_id and all of their children and grandchildrens.

So i found this code below on stackoverflow. but couldn't use it in mysql as im using an older version and also there is a difference in my case. (using 3 columns instead of 2)

My version of mysql doesnot support with.

How can we rewrite the query without using WITH statement.

Thanks in advance.

DECLARE @Id int = your_UnitId
;WITH cte AS 
 (
  SELECT a.Id, a.parentId, a.name
  FROM customer a
  WHERE Id = @Id
  UNION ALL
  SELECT a.Id, a.parentid, a.Name
  FROM customer a JOIN cte c ON a.parentId = c.id
  )
  SELECT parentId, Id, name
  FROM cte
Abdul K Shahid
  • 504
  • 1
  • 4
  • 17
  • 2
    Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Arnaud Peralta Jul 16 '19 at 14:04
  • You could use sub queries in the place of CTEs but it is going to take some effort on your part. CTEs are so popular because of their flexibility and ease of comprehension. It may be easier for you to upgrade to 8.0 than writing sub queries. – Dave Stokes Jul 16 '19 at 14:09
  • 2
    This is not a simple CTE but a **Recursive CTE**. There's no equivalent in MySQL 5.x. The trick mentioned by @ArnaudPeralta works for some specific cases, but it has several limitations. – The Impaler Jul 16 '19 at 14:15
  • This is an easily found duplicate question. See [ask], other [help] links & the voting arrow mouseover texts. PS Please don't insert EDITs/UPDATEs, just make your post the best presentation as of right now. – philipxy Jul 16 '19 at 21:00

1 Answers1

0

If there is a way to re-engineer these tables, I would suggest you take a look at Nested Set Models (Joe Celko). Otherwise, tables like this become really messy with joins upon joins on themselves... but if you must know the query to get the children and grandchildren, it's simply a series of union-ed statements:

Note: My mySql is a bit rusty, so you might need to change the syntax a little to make it work.

SELECT
  id,
  field_id,
  NULL AS parent_id,
  NULL AS grandparent_id,
  0 AS level_depth
FROM
  customer
WHERE
  parent_id = 0
UNION
SELECT
  child.id,
  parent.id AS parent_id,
  NULL AS grandparent_id,
  child.field_id,
  1 AS level_depth
FROM
  customer AS parent
  JOIN customer AS child
    ON child.parent_id = parent.id
WHERE
  parent.parent_id = 0
UNION
SELECT
  child.id,
  child.field_id,
  parent.id AS parent_id,
  grandparent.id AS grandparent_id,
  2 AS level_depth
FROM
  customer AS grandparent
  JOIN customer AS parent
    ON parent.parent_id = grandparent.id
  JOIN customer AS child
    ON child.parent_id = parent.id
WHERE
  grandparent.id = 0
Paurian
  • 1,372
  • 10
  • 18
  • 1
    The WHERE clauses are just anchors. You could conceivably remove that in order to identify a broader level of relationships, such as your "8"'s row - to show that it's the child of field_id "4" and the grandchild of field_id "2" ... but doing it this way, to show that it's the great-grandchild of field_id "1", you'll need to create another UNIONed select and expand it to include another customer table instance as "greatgrandparent". – Paurian Jul 16 '19 at 14:28