First of i am PHP Developer , and working on huge project alone .i think i have made many mistake in creating table. and below is one of those.
I am making packet type system when in my Packet Table , parent packet ( id which is primary key ) and its N -Sub Packet is under (parent_id) is stored , below is my table structure :
Packet_table
id | packet_name | parent_id |
------------------------------
1 | 01 | 0
2 | 02 | 0
3 | 03 | 1
4 | 04 | 1
5 | 05 | 1
6 | 06 | 4
7 | 07 | 4
8 | 08 | 3
9 | 09 | 5
10 | 010 | 2
........................so on and on with N packets in same table
Below is what i have tried but its not getting id N sub packet detail properly:
SELECT p.`packet_name` AS MAIN, s.`packet_name` AS SUB
FROM packet_table s
LEFT JOIN packet_table p ON s.`parent_id` = p.`id`
thats as per above table : id ( which is primary / auto increment )
id = 1 -> main packet (01) , its sub and N sub packets are :
01 - > 03,04,05
04 -> 06,07
03 -> 08
05 -> 09
in short
01 -> 03 -> 08
04 -> 06 , 07
05 -> 09
its not necessary above design format mysql code .. just simple N sub packet query will do
above is just few but in my case there will be N number of sub packet for each (id).
above packet primary id is used in many other tables , so i will get all information in future ?
Note : it can be same as Category and N sub Category type, also please note as said above i don't want query as above explained ( as tree ) .. i just need only mysql query in any format ..
1 } just need is when i search with id=1 then result will give me it all sub packet and it N sub sub packets,
2 } when i search with any sub packet id then it result should give me its sub packet and its all N sub packet and so on and on.. also this sub packet is of which main packet and vise verse.
UPDATE : please check below format for query i need some with N Packet
Table Format 1 : thats Main Packet
+------------+------------+----------+
| Main Pkt | Sub Packet | COUNT(*) |
+------------+------------+----------+
| 01 | 03 | 1 |
| 01 | 04 | 1 |
| 01 | 05 | 1 |
--------------------------------------
Second :
Table Format 2 : thats Sub and Its N sub Packet
+------------+------------+-----------------+
| Main Pkt | Sub Packet | N Sub Packet |
+------------+------------+-----------------+
| 01 | 03 | 08 |
| 01 | 04 | 06 |
| 01 | 04 | 07 |
| 01 | 05 | 09 |
---------------------------------------------
Third :
Table Format 2 : thats Sub and Its N sub Packet
+------------+------------+-----------------+
| Main Pkt | Sub Packet | N Sub Packet |
+------------+------------+-----------------+
| 01 | 03 | 08 |
| 01 | 04 | 06 |
| 01 | 04 | 07 |
| 03 | 011 | 014 | -- *****
---------------------------------------------
above ***** : here 03 is actually sub packet of 01 hecene it query will also help me
So that 01 - 03 - 011 - 014
