SQL - Merge order table and products

Question

I have the following table of REQUESTS:

 `PEDIDOS`(`PEDID`, `PEDIDUSUARIO`, `PEDSTATUS`, `PEDVAL`, `PEDFORMAPAG`, `PEDTROCO`, `PEDDATA`)

And the following product table, where each inserted row contains the order id to which that product belongs:

 `PRODUTOS`(`PRODID`, `PRODPEDID`, `PRODNOME`, `PRODQUANT`, `PRODVAL`)

Therefore, for each record in REQUEST there is one or many records on PRODUCTS (order = shopping cart, products = products in cart).

I am listing all orders successfully, but how I could query the products in the same query?

Use PHP and return the data in JSON, my only problem is the same SQL.

Answer 1

You find yourself in a typical relationship n,m.

In this case, your problem of products included in sales is the same problem of students enrolled in disciplines:

In this case, the above scheme is in the Entity-Relationship Model, or MER. This model is very useful for making quick data modeling, and also for having a deep understanding of the business involved. However, not everything is flowers. SQL is not directly compatible with MER, because SQL follows the Relational Model, MR, based on relational algebra.

To turn from MER to MR, it is very important to know the aridity of the relationship. In your case, the interesting part is in the relationship many-to-many, in English Many-to-Many.

More about turning MER into MR in this answer

In a relationship Many-to-Many, you have m entities of a type relating to n entities of the other type. In this case, you create an intermediate table with foreign keys for the two entity tables. Example:

I sold a drill and two saws.

My table of entity produto:

produto
| prodid | prodnome  |
+--------+-----------+
| 1      | serrote   |
| 2      | furadeira |

My table of entity pedido:

pedido
| pedid |
+-------+
| 1984  |

My table for relationship:

item
| itemid   | prodid | pedid | quant | valor  |
+----------+--------+-------+-------+--------+
| 1783     | 1      | 1984  | 2.0   | 10.00  |
| 2123     | 2      | 1984  | 1.0   | 150.00 |

To find out which products (and their quantities) are in the 1984 application:

SELECT
    prod.prodnome,
    i.quant
FROM
    pedido ped
        inner join item i on (ped.pedid = i.pedid)
        inner join produto prod on (i.prodid = prod.prodid)
WHERE
    ped.pedid = 1984

In my case, I am modeling according to needs I have already passed in the company where I work. There are cases where you want to sell the same item two or more times in the same order (for example, as bonus item, exchange and sale itself), so I define your primary key as itemid. In most cases, however, a relationship n,m usually does not have the repetition of entities.

Answer 2

2 options:

• INNER JOIN
• LEFT OUTER JOIN

With inner join

SELECT campo_tabela1,campo_tabela2
FROM TABELA_1
INNER JOIN TABELA_2
ON idx_tabela2 = idx_tabela1

In this case you will get SO results in the case where INNER JOIN is OK. If you have in TABELA_1, a record that does not have idx_table2 = idx_table1, it will not appear. Then case of a customer table + a product table vc will have only customers that has product. Customers who have no products will not show up.

With LEFT OUTER JOIN

SELECT campo_tabela1,campo_tabela2
FROM TABELA_1
LEFT OUTER JOIN TABELA_2
ON idx_tabela2 = idx_tabela1

In this case, in the result you will have the TABELA_1 data which has idx_tabela2 = idx_tabela1 plus you will also have the others. That is, you will have customers who have products + customers who do not have. In the case of customers who have no products, the value of the product table fields will be "null" which is a well specified value in SQL.