1

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

  • 1 is connected to 0
  • 2 is connected to 1
  • 3 is connected to 2

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?

Teito
  • 13
  • 4
  • Yes, I believe you'll have to use at least one AND in your WHERE clause...is there a simple linear relationship between sponsor_id and key or is it more random? – The One and Only ChemistryBlob Aug 06 '14 at 00:27
  • No, there's no relationship between the key and sponsor_id. the key is more just like a autoincrement field for that table. the sponsor_id is a foregin_key let's say from another table. – Teito Aug 06 '14 at 00:31
  • I'm not sure I totally understand your data relationships. It sounds like a simple join, but I suspect I'm missing something in your question. Are you familiar with joins and sub queries? – Trebor Aug 06 '14 at 00:35
  • Edit: sorry. the sponsor_id is from the key itself. – Teito Aug 06 '14 at 01:02
  • You are asking for a recursive or hierarchical query. MySQL provides basically no support for this. You can do it in a stored procedure. – Gordon Linoff Aug 06 '14 at 02:47

1 Answers1

1

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 |
Community
  • 1
  • 1
Rachael
  • 424
  • 2
  • 7