How to Get Data from an N Relationship for N

Question

Eu tenho uma tabela tb_produtos com idproduto, desproduct, outra tabela tb_categorias com idcategory, descategory, e por fim uma ultima tabela product_category com idproduct e idcategory.

If I want to, for example, take all the products that have as idcategory 1 how would I do this search using also the product_category table? I have much this doubt of how to get data from tables with relationship N to N

Answer 1

You can build the query in several ways, because the JOINS between tables has N possibilities. But one of the forms would be:

SELECT pc.*,pro.desproduct,cat.descategory from tb_produtos as pro
INNER JOIN product_category AS pc ON pc.idproduto = pro.idproduto
INNER JOIN tb_categorias  AS cat ON pc.idcategory = cat.idcategory

OBS: using Inner Join the ids cannot be null

Ex:

tb_products

idproduto | desproduct

1            ABC
2            XYZ

tb_categories

idproduto | descategory

1         categoria1
2         categoria2

product_category

idproduto | idcategory

1           1
1           2
2           1

When executing the mentioned query the result would be

idproduto | idcategory | desproduct | descategory

 1           1             ABC      categoria1
 1           2             ABC      categoria2
 2           1             XYZ      categoria1

Product 1 belongs to Category 1 and 2 and product 2 belongs to category 1.