How to fetch data from one table using reference from others?

Question

How to fetch data from one table using reference from others?

Navigation

#1 by (1 votes)

2

I'll illustrate with one simple thing ...

I have a 'products' table that has columns, id, product_name, value. I have another table 'users' columns, id, name. And another table 'user purchases' id, id_user, product_id, product_name, this third table is generated from these first two ... until then OK.

I want to search the 'products' table for all items except for items that have 'id_user' (from the purchasing 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 in the list anymore. How do I use this with select?

mysql query

asked by anonymous 26.09.2017 / 16:31

1 answer

How to change the size of a screen through an event in C # WPF How to return values from two distinct tables php

score 1 · Accepted Answer

Use the EXISTS clause 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 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