Page 1 of 1

Select record based on the result (subqueries help) : 3.23

Posted: Thu Jan 13, 2005 3:15 am
by santana
Hi All


I have a table for network scheme (multilevel marketing),

Code: Select all

mysql> select * from structure;
+----+--------+------+------+------+------+------+
| id | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  |
+----+--------+------+------+------+------+------+
|  1 |      1 | 2    | 5    | 14   | NULL | NULL |
|  3 |      1 | 3    | 8    | 23   | NULL | NULL |
|  5 |      1 | 4    | 11   | NULL | NULL | NULL |
|  2 |      2 | 5    | 14   | NULL | NULL | NULL |
|  8 |      2 | 6    | 17   | NULL | NULL | NULL |
| 10 |      2 | 7    | 20   | NULL | NULL | NULL |
|  4 |      3 | 8    | 23   | NULL | NULL | NULL |
| 13 |      3 | 9    | 26   | NULL | NULL | NULL |
| 15 |      3 | 10   | NULL | NULL | NULL | NULL |
|  6 |      4 | 11   | NULL | NULL | NULL | NULL |
| 18 |      4 | 12   | NULL | NULL | NULL | NULL |
| 20 |      4 | 13   | NULL | NULL | NULL | NULL |
|  7 |      5 | 14   | NULL | NULL | NULL | NULL |
|  9 |      6 | 17   | NULL | NULL | NULL | NULL |
| 11 |      7 | 20   | NULL | NULL | NULL | NULL |
| 12 |      8 | 23   | NULL | NULL | NULL | NULL |
| 14 |      9 | 26   | NULL | NULL | NULL | NULL |
| 16 |     10 | NULL | NULL | NULL | NULL | NULL |
| 17 |     11 | NULL | NULL | NULL | NULL | NULL |
| 19 |     12 | NULL | NULL | NULL | NULL | NULL |
| 21 |     13 | NULL | NULL | NULL | NULL | NULL |
| 22 |     14 | NULL | NULL | NULL | NULL | NULL |
| 23 |      5 | 15   | NULL | NULL | NULL | NULL |
| 24 |     15 | NULL | NULL | NULL | NULL | NULL |
| 25 |      5 | 16   | NULL | NULL | NULL | NULL |
| 26 |     16 | NULL | NULL | NULL | NULL | NULL |
| 27 |     17 | NULL | NULL | NULL | NULL | NULL |
| 28 |      6 | 19   | NULL | NULL | NULL | NULL |
| 29 |     19 | NULL | NULL | NULL | NULL | NULL |
| 30 |     20 | NULL | NULL | NULL | NULL | NULL |
| 31 |      7 | 21   | NULL | NULL | NULL | NULL |
| 32 |     21 | NULL | NULL | NULL | NULL | NULL |
| 33 |      7 | 22   | NULL | NULL | NULL | NULL |
| 34 |     22 | NULL | NULL | NULL | NULL | NULL |
| 35 |     23 | NULL | NULL | NULL | NULL | NULL |
| 36 |      8 | 24   | NULL | NULL | NULL | NULL |
| 37 |     24 | NULL | NULL | NULL | NULL | NULL |
| 38 |      8 | 25   | NULL | NULL | NULL | NULL |
| 39 |     25 | NULL | NULL | NULL | NULL | NULL |
| 40 |     26 | NULL | NULL | NULL | NULL | NULL |
| 41 |      9 | 27   | NULL | NULL | NULL | NULL |
| 42 |     27 | NULL | NULL | NULL | NULL | NULL |
| 43 |      9 | 28   | NULL | NULL | NULL | NULL |
| 44 |     28 | NULL | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
44 rows in set (0.00 sec)
* This number representing IDs


We want to select all record based on the 'related leader' column.
Here is an example. The 1st row is

Code: Select all

mysql> select * from structure;
+----+--------+------+------+------+------+------+
| id | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  |
+----+--------+------+------+------+------+------+
|  1 |      1 | 2    | 5    | 14   | NULL | NULL |
we need to do query again based on the 1st, 2nd, 3rd, th and 5th column.

Code: Select all

mysql> select * from structure where leader = '2';
+----+--------+------+------+------+------+------+
| id | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  |
+----+--------+------+------+------+------+------+
|  2 |      2 | 5    | 14   | NULL | NULL | NULL |
|  8 |      2 | 6    | 17   | NULL | NULL | NULL |
| 10 |      2 | 7    | 20   | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
3 rows in set (0.00 sec)

Code: Select all

mysql> select * from structure where leader = '5';
+----+--------+------+------+------+------+------+
| id | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  | 
+----+--------+------+------+------+------+------+
|  7 |      5 | 14   | NULL | NULL | NULL | NULL |
| 23 |      5 | 15   | NULL | NULL | NULL | NULL |
| 25 |      5 | 16   | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
3 rows in set (0.00 sec)

Code: Select all

mysql> select * from structure where leader = '14';
+----+--------+------+------+------+------+------+
| id | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  | 
+----+--------+------+------+------+------+------+
| 22 |     14 | NULL | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+------+------+
1 row in set (0.00 sec)

All these values should be returned.

What is the best method to do all this using mySQL 3.23

How can I do this in PHP?


Thanks

Posted: Thu Jan 13, 2005 5:43 am
by scorphus
Hi, santana. Welcome to DevNetwork!

This can be performed by a JOIN statement. Please refer to the JOIN reference in the MySQL Manual: http://dev.mysql.com/doc/mysql/en/JOIN.html

Here is a query that might work:

Code: Select all

SELECT s1.id, s2.*
FROM structure s1
LEFT JOIN structure s2 ON s2.leader IN(s1.1st, s1.2nd, s1.3rd, s1.4th, s1.5th)
HAVING s1.id = 1;
Which outputs:

Code: Select all

+------+------+--------+------+------+------+------+------+
| id   | id   | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  |
+------+------+--------+------+------+------+------+------+
|    1 |    2 |      2 |    5 |   14 | NULL | NULL | NULL |
|    1 |    8 |      2 |    6 |   17 | NULL | NULL | NULL |
|    1 |   10 |      2 |    7 |   20 | NULL | NULL | NULL |
|    1 |    7 |      5 |   14 | NULL | NULL | NULL | NULL |
|    1 |   22 |     14 | NULL | NULL | NULL | NULL | NULL |
|    1 |   23 |      5 |   15 | NULL | NULL | NULL | NULL |
|    1 |   25 |      5 |   16 | NULL | NULL | NULL | NULL |
+------+------+--------+------+------+------+------+------+
7 rows in set (0.00 sec)
For the entire table you can take out the HAVING clause:

Code: Select all

SELECT s1.id, s2.*
FROM structure s1
LEFT JOIN structure s2 ON s2.leader IN(s1.1st, s1.2nd, s1.3rd, s1.4th, s1.5th)
ORDER BY s1.id;
Which outputs:

Code: Select all

+------+------+--------+------+------+------+------+------+
| id   | id   | leader | 1st  | 2nd  | 3rd  | 4th  | 5th  |
+------+------+--------+------+------+------+------+------+
|    1 |    2 |      2 |    5 |   14 | NULL | NULL | NULL |
|    1 |    8 |      2 |    6 |   17 | NULL | NULL | NULL |
|    1 |   10 |      2 |    7 |   20 | NULL | NULL | NULL |
|    1 |    7 |      5 |   14 | NULL | NULL | NULL | NULL |
|    1 |   22 |     14 | NULL | NULL | NULL | NULL | NULL |
|    1 |   23 |      5 |   15 | NULL | NULL | NULL | NULL |
|    1 |   25 |      5 |   16 | NULL | NULL | NULL | NULL |
|    2 |   25 |      5 |   16 | NULL | NULL | NULL | NULL |
|    2 |    7 |      5 |   14 | NULL | NULL | NULL | NULL |

їsnip]

|   42 | NULL |   NULL | NULL | NULL | NULL | NULL | NULL |
|   43 |   44 |     28 | NULL | NULL | NULL | NULL | NULL |
|   44 | NULL |   NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+--------+------+------+------+------+------+
73 rows in set (0.00 sec)
Hope this works for you.

-- Scorphus

Posted: Thu Jan 13, 2005 6:04 am
by santana
Hi scorphus


Thanks! I'll try it out later tonight.

You have given me some light here :)



Thanks

Posted: Thu Jan 13, 2005 7:58 am
by feyd
Moved to Databases.

Posted: Thu Jan 13, 2005 8:53 pm
by santana
Wonderful! scorphus!

You da man!

:P


Thank you.