Help with Select query in Mysql

Asked

Viewed 60 times

1

I have the "customers" table and I have another one called "products". Each customer has several products where the products table tuples have the customer id to reference.

I need a Select that returns to me all customers who own the Code 19 product but do not own the Code 22 product.

I made some attempts here with Select nested but could not get the result.

Could you help me set up this consultation?

Clientele:

  • IDCLIENTE
  • NAME

Table Products Customers:

  • IDTABELA
  • 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 customer records that have these 2 products, I wanted now this result remove the ones that have the code 22.

mysql

  • Show how the tables look. Post how you are trying to make it easier.

  • tried doing with a complete joint removing the products in Where? SELECT * FROM CLIENTES&#xA; INNER JOIN PRODUTOS ON PRODUTOS.CLIENTE_ID = CLIENTES.ID&#xA; WHERE PRODUTOS.CODIGO = 19 AND PRODUTOS.CODIGO <> 22

  • @Leonardopaim when going to add data on your question, edit the question and post on it. In the comments it becomes a mess and complicates the visualization.

1 answer

2


I believe that 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)

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.