Search code examples
mysqlrdbms

MySQL MLM downline select query for the very first user

I am running a select query to get the downline members. It works nice for all users except the first user.

Here is my sql fiddle with data http://sqlfiddle.com/#!9/036c5e/2

I want to get the downline members for user_id 1

I am running the query like this 

SELECT 
            t1.user_id AS lev0, 
            t2.user_id as lev1, 
            t3.user_id as lev2, 
            t4.user_id as lev3,
            t5.user_id as lev4,
            t6.user_id as lev5,
            t7.user_id as lev6,
            t8.user_id as lev7,
            t9.user_id as lev8

        FROM members AS t1

            LEFT JOIN members AS t2 ON t2.boss = t1.user_id
            LEFT JOIN members AS t3 ON t3.boss = t2.user_id
            LEFT JOIN members AS t4 ON t4.boss = t3.user_id
            LEFT JOIN members AS t5 ON t5.boss = t4.user_id
            LEFT JOIN members AS t6 ON t6.boss = t5.user_id
            LEFT JOIN members AS t7 ON t7.boss = t6.user_id
            LEFT JOIN members AS t8 ON t8.boss = t7.user_id
            LEFT JOIN members AS t9 ON t9.boss = t8.user_id
            WHERE t1.user_id = 1
            order by t1.created_at

If I replace t1.user_id =2 or any others it works nicely. Anyone please help. Thanks..

EDIT I want to get the user_id of boss 1. boss 1 is the starter. So I want to get all his downlines members. How can I do this? Many thanks.

Solution

  • Change

    WHERE t1.user_id = 1

    To

    WHERE t1.boss = 1