1
I need to show the products that have not yet been sold (were not involved in any sale), grouped by Type and then by Manufacturer, and in ascending alphabetical order by name.
I did some research before opening the topic, but I didn’t get anywhere, this code was one of the ones I saw in my research, but only one that ended up frustrating me.
How far I’ve come:
select idproduto from vendas e where not exists
(select * from produtos r where e.idproduto = r.id);
Exit:
Empty set (0.00 sec)
Structure of my tables:
mysql> select * from produtos;
+------+------------------------+------------+------------+------------+------------+
| ID | Nome | Fabricante | Quantidade | VlUnitario | Tipo |
+------+------------------------+------------+------------+------------+------------+
| 1 | Playstation 3 | Sony | 100 | 2000 | Console |
| 2 | Core 2 Duo 4GB RAM 500 | DELL | 200 | 1900 | Notebook |
| 3 | XBOX 360 120 GB | Microsoft | 350 | 1300 | Console |
| 4 | GT-1620 QUAD BAND | SAMSUNG | 300 | 500 | Celular |
| 5 | iPHONE 4 32 MB | Apple | 50 | 1500 | Smartphone |
| 6 | Playstation 2 | Sony | 100 | 400 | Console |
| 7 | Wii 120 MB | Nintendo | 250 | 1000 | Console |
| 8 | S7 | SAMSUNG | 100 | 1500 | Smartphone |
| 9 | J7 | SAMSUNG | 300 | 1000 | Smartphone |
| 10 | iPHONE 5 32 MB | Apple | 150 | 2000 | Smartphone |
| 11 | Core 2 Duo 8 GB | DELL | 100 | 2500 | Notebook |
| 12 | Playstation 4 | Sony | 250 | 2500 | Console |
| 13 | Notebook | DELL | 200 | 3000 | Notebook |
| 14 | Ultrabook | DELL | 100 | 5500 | Ultrabook |
| 15 | S9 | SAMSUNG | 300 | 2500 | Smartphone |
+------+------------------------+------------+------------+------------+------------+
15 rows in set (0.05 sec)
mysql> select * from vendas;
+---------+------------+----------------+-----------+----------+----------+
| IDVenda | Data | Cliente | IDProduto | QtdVenda | Desconto |
+---------+------------+----------------+-----------+----------+----------+
| 1 | 2017-07-08 | Carlos Santana | 15 | 10 | 50 |
| 2 | 2017-07-09 | Ana Maria Melo | 7 | 1 | 100 |
| 3 | 2017-07-09 | Fernando Silva | 8 | 1 | 70 |
| 4 | 2017-07-09 | Fernando Souza | 13 | 2 | 150 |
| 5 | 2017-07-10 | Luis Gama | 3 | 1 | 50 |
| 6 | 2017-07-10 | Arthur Filho | 7 | 2 | 100 |
| 7 | 2017-07-10 | Fernando Silva | 12 | 1 | 50 |
| 8 | 2017-07-10 | Fernando Souza | 15 | 3 | 200 |
| 9 | 2017-07-12 | Ana Maria Melo | 1 | 3 | 200 |
| 10 | 2017-07-13 | Cassio Moreira | 5 | 1 | 50 |
+---------+------------+----------------+-----------+----------+----------+
10 rows in set (0.02 sec)
My question is in the part where the statement asks to list the products not yet involved in any sale.
I managed with this code to show the products involved in transactions: select distinct idproduct from sales, products Where id = idproduct group by idproduct.
– Matheus Rocha
SELECT * FROM produtos p LEFT JOIN vendas v ON p.id = v.idproduto WHERE v.idproduto IS NULL;- In other words, "Return all of the LEFT relating by the product, where there is no product".– Bacco
Reverse your edit, because the question field should contain only the question, Two things: (1) if you want, you can post as an answer in the bottom field, just leave a message here that I temporarily reopen the post for this. (2) Realize that what you did in terms of performance and memory consumption is much more complicated than JOIN. JOIN makes simple comparison, its solution requires to store all the Ids to then compare one to one generating a number of comparisons that is equivalent to the number of lines on one side multiplied by the number of lines on the other, which is bad in this scenario.
– Bacco