Packet and N-SubPacket Msql query ( same as catgeory and N sub catgeory )

Question

I am making packet type system Where 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).

Note : it can be same as Category and N sub Category type, also pls 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 Packets

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  hence it query will also help me

So that 01 - 03 - 011 - 014

As you've discovered, MySQL does not support recursion 'out-of-the-box'. Choices include joining the table to itself as often as could be required, creating some kind of sproc, switching to an alternative data model (e.g. nested set), or handling the recursion logic at the application level (in this case, with a PHP loop). All of these options have been discussed with examples (here, and elsewhere) ad nauseam. — Strawberry, Sep 10 '14 at 09:54
OHH AFTER 5 DAYS I GOT ONE REPLY : THANKS strawberry , can u help me with the link of (here, and elsewhere) :) would be great or with code itself :) thanks — user3209031, Sep 10 '14 at 12:28

score 1 · Answer 1 · edited May 23 '17 at 11:58

In order to be able to get all children in one query, you can restructure the table so it stores the packet data as a nested-set model.

A tree structure built from your data sample:

        0
      /   \
     1     2
   / | \    \
  3  4  5    10
  |  |\  \
  8  6 7  9

Table:

+-------+--------------+-----+-----+
|    id | packet_name  | lft | rgt |
+-------+--------------+-----+-----+
|     0 | 00           |   1 |  22 |
|     1 | 01           |   2 |  17 |
|     2 | 02           |  18 |  21 |
|     3 | 03           |   3 |   6 |
|     4 | 04           |   7 |  12 |
|     5 | 05           |  13 |  16 |
|     6 | 06           |   8 |   9 |
|     7 | 07           |  10 |  11 |
|     8 | 08           |   4 |   5 |
|     9 | 09           |  14 |  15 |
|    10 | 010          |  19 |  20 |
------------------------------------

Get all nodes of a subtree with the root 01:

SELECT node.packet_name
FROM Packet_table AS node,
    Packet_table AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND parent.packet_name = '01'
ORDER BY node.lft;

Output:

01, 03, 08, 04, 06, 07, 05, 09

Another solution is to have a loop on the PHP side and fetch all children recursively (see recursive function to get all the child categories).

Packet and N-SubPacket Msql query ( same as catgeory and N sub catgeory )

1 Answers1