Query with Select query in MySql

Question

Query with Select query in MySql

Navigation

#1 by (2 votes)

1

I have the "customers" table and I have another with the name "products". Each client has several products where the tuples of the products table have the customer id to make the reference.

I need a Select that returns all customers who own the product of code 19 but do not have the product of code 22.

I made a few attempts here with Select nested but did not get the result.

Could you help me mount this query?

Customers table:

IDCLIENTE
NAME

Customers Products table:

IDTABLE
IDPRODUTO
IDCLIENTE

I tried to implement the following logic:

select
    IDCLIENTE,
    IDPRODUTO
from
    CLIENTES C
    inner join PRODUTOS_CLIENTES P on C.IDCLIENTE = P.IDCLIENTE
where
    P.IDPRODUTO not in (
        select
            IDPRODUTO
        from
            PRODUTOS_CLIENTES
        where
            IDPRODUTO <> 19
            and IDPRODUTO <> 22
    )

With this I got all the registrations of customers that have these 2 products, I wanted to remove the ones with the 22 code.

mysql

asked by anonymous 06.02.2018 / 16:52

1 answer

How to simplify the process of classifying a hash based on values CORS error when executing request using HttpClient in the Angular

score 2 · Accepted Answer

I think this would solve your problem:

SELECT * FROM CLIENTES
WHERE CLIENTES.IDCLIENTE IN
   (SELECT IDCLIENTE FROM PRODUTOS_CLIENTES
   WHERE IDPRODUTO = 19)
AND CLIENTES.IDCLIENTE NOT IN
   (SELECT IDCLIENTE FROM PRODUTOS_CLIENTES
   WHERE IDPRODUTO = 22)