Category query in Mysql

Question

I’m learning Mysql, I need help, I need to perform a query that returns 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 need to list sales by category through the "product id" by the operator

You can help me, please?

Answer 1

Come on, if I understand your question correctly you need to make an appointment that returns sales from a given one category.

It turns out that category is not present in sales table, it’s not even?

How to solve this?

To sale has a product, and this yes has a category. So we used the relationship between tables, through the concept we call foreign key (FK - Foreign Key), to obtain the desired information. We do this through JOIN (http://dev.mysql.com/doc/refman/5.7/en/join.html).

Follow an example of SELECT to illustrate (based on the fields you spent).

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;