3

Hi I need a query in MySQL for my PHP page, I give my table design 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 is when I pass arul code in data ie 101, I need to get the whole left and right of him example output as

LEFT         Right
James        Alex
David        Prem
Sasi         Kumar

if someone here can help me out without using storedprocdure, it will be very helpful and thanks in advance :)

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
Arul Kumar
  • 71
  • 5
  • When there is no data it will exit. Or you can say like once all the data has been fetched it have to get exited. – Arul Kumar May 10 '12 at 07:21
  • 1
    MySQL doesn't support recursive functions, so it is not well suited to the adjacency list model of storing hierarchical data. You ought to consider restructuring your data to use either nested sets or closure tables. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. – eggyal May 10 '12 at 07:39

1 Answers1

1

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

Here is a possibility 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;

Of course you can extend the number of answers you need easily.

Olivier G
  • 125
  • 6