Select record based on the result (subqueries help) : 3.23
Posted: Thu Jan 13, 2005 3:15 am
Hi All
I have a table for network scheme (multilevel marketing),
* This number representing IDs
We want to select all record based on the 'related leader' column.
Here is an example. The 1st row is
we need to do query again based on the 1st, 2nd, 3rd, th and 5th column.
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
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)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 |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