0

I am building a multi-level referral system and I would like to offer bonuses on a pyramidal way. My simplified users table has this model:

id | referal
------------
1  |  Null
----------
2  |  1
----------
3  |  2
----------
4  |  2
----------
5  |  4
----------

I was wondering how can I get an entire pyramid just from one mySQL query: Example : Get all refferals pyramid starting with user 5 Correct Answer: 5-4-2-1

I know to code this but Sql is faster!

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Mishu Vlad
  • 281
  • 3
  • 6
  • 1
    It's not clear from your question what exactly you're trying to do. What, for instance, do you mean by "a pyramidal way"? – BadIdeaException Mar 27 '14 at 17:03
  • 2
    Have a look at Common Table Expressions and recursion - that's the way I would approach it... – Martin Milan Mar 27 '14 at 17:04
  • I would like to do a website, so mySQL would be perfect! – Mishu Vlad Mar 27 '14 at 17:09
  • 2
    MySQL is the one database that *doesn't* support CTEs and recursive queries, which is the most elegant way to do hierarchical queries. It's not the only database used for web sites either. – Panagiotis Kanavos Mar 27 '14 at 17:13
  • if you are trying to stay free but need more advance DB you might want to consider switching to [tag:PostgreSQL] which indeed supports recursive CTE http://www.postgresql.org/docs/8.4/static/queries-with.html –  Mar 27 '14 at 17:18

0 Answers0