Geek asks and answers

Need a query for multi-level marketing in mysql

Hi, I need a query in MySQL for my PHP page, I give a table with data:

tablename: tcustomers

Name, LeftID, RightID, Code
---------------------------
Arul    102      103     101
James   104      105     102
Alex    106      107     103
David   108      109     104
Sasi    110      111     105
Prem    112      113     106
Kumar   114      115     107

what i need when i pass the arul code in the data, i.e. 101, i need to get all left and right from it, like for example

LEFT         Right
James        Alex
David        Prem
Sasi         Kumar

if someone here can help me without using a stored procedure, it will be very useful and thanks in advance :)

+3
source share
1 answer

This is a partial solution if you want only a finite number of answers for the right and left elements .

Here is an opportunity with 3 right and 3 left answers with the condition Code = 101 (Arul):

SELECT
    c.Name as NameRequested,
    l1.Name as NameLeft1,
    l2.Name as NameLeft2,
    l3.Name as NameLeft3,
    r1.Name as NameRight1,
    r2.Name as NameRight2,
    r3.Name as NameRight3
FROM tcustomers c
LEFT JOIN tcustomers l1 ON c.LeftID = l1.Code
LEFT JOIN tcustomers l2 ON l1.LeftID = l2.Code
LEFT JOIN tcustomers l3 ON l2.LeftID = l3.Code
LEFT JOIN tcustomers r1 ON c.RightID = r1.Code
LEFT JOIN tcustomers r2 ON r1.RightID = r2.Code
LEFT JOIN tcustomers r3 ON r2.RightID = r3.Code
WHERE
    l.Code = 101;

, , .

+1

More articles:


All Articles