Select until in MySQL

Asked Aug 22 '18 at 19:07

Active Aug 22 '18 at 19:44

Viewed 81 times

I have this table the contains user_id and father_id. I want to select all the father_ids that are associated with the user_id until the value of the father_id = 0 and store them in a comma-separated format.

Table: network

select user_id, father_id from network where user_id = 1762;

user_id father_id
1762    1761

select user_id, father_id from network where user_id = 1761; -- resulting father_id from the first query

user_id father_id
1761    1760

select user_id, father_id from network where user_id = 1760; -- resulting father_id from the second query

user_id father_id
1760    0

In this example, I want to get all the father_ids until i reached father_id = 0 by using the resulting father_id as the next user_id.

edited Aug 22 '18 at 19:44

sticky bit

36,626
12
31
42

asked Aug 22 '18 at 19:07

Morgi

1

What version of MySQL? In MySQL 8.0 you can do this using a single SQL. In MySQL 5.x you need multiple ones. – The Impaler Aug 22 '18 at 19:13
I'm using MySQL 5.5. I will try to update to version 8.0 if it is simpler. – Morgi Aug 22 '18 at 19:15
A workaround is to use a variable. – The Impaler Aug 22 '18 at 19:17
Sorry for asking too much, I'm just new to this. A little bit of head start will be a great help. Thanks! – Morgi Aug 22 '18 at 19:29
See this answer: https://stackoverflow.com/questions/28252370/selecting-hierarchical-data-using-mysql-variable – The Impaler Aug 22 '18 at 19:30
Update to MySQL 8+. You are storing hierarchical data and earlier versions of MySQL have basically no support for such data. – Gordon Linoff Aug 22 '18 at 19:37

Select until in MySQL

0 Answers0