Category query in MySQL

Question

Category query in MySQL

Navigation

#1 by (3 votes)

1

I'm learning MySQL, I need a help, I need to make a query that returns me a sales list by product category.

++++++++++++++++++++++++++++++++++++++++++++++
tb categoria 
campos: (id, categoria) 
dados: (12, camisas)
++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++
tb produto 
campos: (id, categoria_id, nome produto)
dados: (1 , 12, camisas leves)
++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++
tb compra 
campos: (id, produto_id, id_vendedor) 
dados: (5, 1, 23)

++++++++++++++++++++++++++++++++++++++++++++++

In tb purchase I need to list the sales by category through the "product id" by the operator

Can you help me, please?

mysql inner

asked by anonymous 09.06.2016 / 23:35

1 answer

How to compare with Guid Mvc Connect server to local network by hostname

score 3 · Accepted Answer

Come on, if I understand your question right you need to make an inquiry that returns sales from a particular category .

It happens that category is not present in the sales table , is it?

How to solve this?

Sale has a product , and it does have a category . So we use the relationship between tables , through the concept we call a foreign key (FK) to get the desired information. We do this through JOIN operations ( link ).

Here is an example of SELECT to illustrate (based on the fields you passed).

SELECT c.*
  FROM tb_compra c
 INNER JOIN tb_produto p ON p.id = c.produto_id
 INNER JOIN tb_categoria ct ON ct.id = p.categoria_id
 WHERE ct.id = 12;