I need to show products that have not yet been sold (not involved in any sales), grouped by Type and then by Manufacturer, and in ascending alphabetical order by name.
I checked one before opening the topic, but I did not get anywhere, this code was one of those I saw in my searches, but only one that frustrated me. >
As far as I've come:
select idproduto from vendas e where not exists
(select * from produtos r where e.idproduto = r.id);
Output:
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.