How does mysql query many-to-many relational tables?

Table relationship:

question:
how to query the data shown in the figure using native sql statements?
expected data structure (the list item of the product appears as a nested object):

{
  "category": [
    {
      "id": 1,
      "name": "name1",
      "product":[
          {
            "id":2,
            "detail":""
          },
          {
            "id":8,
            "detail":""
          }
      ]
    }
  ]
}
Dec.13,2021

there are two ways,

  1. first query category , and then traverse category to find product of each category , which will lead to Number1 query problems
  2. first query category , and then query all category.id to in , and match them in memory according to the relationship, as long as 2 times.

recommend the second kind, Chestnut:

  1. take out the category list you want

    select * from category
  2. 1 id is put into in joint queries category_product and product to get product list

    select category_id, product.id, product.detail from category_product where category_id in (...)
    left join product 
    on category_product.product_id=product.id
  3. put the product obtained in 2 into the corresponding category.product array in 1 according to category_id .

    ...
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7c3a14-166c5.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7c3a14-166c5.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?