Create select with multiple fields

3

How to create a select that makes the relation of at least 03 tables and present a field of each table at least?

    
asked by anonymous 21.06.2016 / 01:14

1 answer

3

In a relational database we store the data in tables. These tables are related by foreign keys (FK - Foreign Key ) .

Normally the primary key of one table will be present in another, which it relates in the form of foreign key .

An example: We have 3 tables: purchase , product and category .

  • purchase has a product (it is product related by the produto_id field, which is Primary Key

    / strong> and foreign key in the purchase table);
  • product has a category (it is category-related by the category_id field, which is primary key for category and > foreign key in the product table);

To make a query that relates these 3 tables we have to use JOIN .

  

Here you can find MySQL documentation for the subject, but the   This concept is generic and applies to virtually all   data ( link ).

A SELECT to illustrate:

SELECT c.data, c.valor, p.nome, ct.descricao
  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;

The query brings the fields data and valor of the purchase table, the nome field of the product table and% category .

I hope I have helped.

    
21.06.2016 / 01:45