CODEWITHSUNDEEP
phpmysql
Teito
Teito

Reputation: 13

mysql: select from a table where key is equal to a value and also select all the rows related to that value directly or indirectly

The scenario is this 

key    |    value   |  sponsor_id
===================================
1      |    mykel   |  0
2      |    sydney  |  1 
3      |    darren  |  2
4      |    luis    |  3

What I want to achieve is something like this

SELECT * FROM tbl WHERE sponsor_id = 0;

So basically, "mykel" field is the on that will be selected. But, I also want to select all the child element of sponsor_id 0;

As stated in the table

In conclusion, I want to select rows starting from the parent index which is 0 and also all the child rows connected to it wether directly or indirectly.

Is it achievable using one straight query?

Upvotes: 1

Views: 1563

Answers (1)

Rachael
Rachael

Reputation: 424

Found this answer that might be helpful to you:

How to do the Recursive SELECT query in MySQL?

select col1, col2, @pv:=col3 as 'col3' from table1
join
(select @pv:=1)tmp
where col1=@pv

SQL FIDDLE: http://sqlfiddle.com/#!2/9635d2/1

| COL1 | COL2 | COL3 |
----------------------
|    1 |    a |    5 |
|    5 |    d |    3 |
|    3 |    k |    7 |

Upvotes: 1

Related Questions