How to search data from a table using other reference?

Question

I’ll give you a simple example...

I have a table 'products' that has in the columns, id, product name, value. I have another table 'users' columns, id, name. And another table 'buysdosusuarios' id, id_user, id_produtos, nomedoproduto, this third table is generated from these first two... until then OK.

I want to search in the table 'products' all items except the items that have 'id_user'(from the shopping table)= 1 for ex

produtos
id          nomedoproduto, valor
1           Roupa           100 
2           Telefone         500
3           Geladeira        1500
4           TV              1000
5           Microondas      300
6           Sofá            600

users
id nome
1 José
2 Felipe

Compras dos usuários
id   id_user id_produtos produtos
1       1         1       Roupa
2       1         2       Telefone
3       1         3       Geladeira
4       2         1        Roupa

Well... if the user has already bought... I want it not to appear on the list anymore. how do I use this with select?

Answer 1

Use the clause EXISTS together with NOT:

SELECT p.*
  FROM produtos p
 WHERE NOT EXISTS(SELECT 1
                    FROM comprasdosusuarios cdu
                   WHERE cdu.id_produtos = p.id
                     AND cdu.id_user = 1)
   AND p.nomedoproduto LIKE '%roupa%'

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will BE TRUE, and NOT EXISTS will BE FALSE