All geek questions in one place

How to find people who bought one product, but NOT another?

I have a table linking customers to previous purchases:

RecID   CustID   ProdID
  1       20      105
  2       20      300
  3       31      105
  4       45      105
  5       45      300
  6       45      312

I would like to get a list of CustID who bought Item 105, but was not, but Item 300.

In this case, CustID 31.

I cannot do this with selects and join. I'm at a dead end!

I would appreciate the help of experienced SQL people.

Thank!

Thank!

I am the original author of the question.

The second Mark Byers example with NOT IN works great! (I have not tried others after this one worked for me).

His first LEFT JOIN example did not return CustIDs ... I think I copied it correctly and used the correct table names and column names. So I don’t know why it didn’t work for me.

Thanks again to everyone who so kindly took the time to write some kind of SQL for me.

, ( , , , )

+3
4

. LEFT JOIN:

SELECT T1.CustID
FROM yourtable T1
LEFT JOIN yourtable T2 ON T1.CustID = T2.CustID AND T2.ProdId = 300
WHERE T1.ProdId = 105
AND T2.ProdId IS NULL

:

SELECT CustID
FROM yourtable 
WHERE ProdId = 105
AND CustID NOT IN
(
    SELECT CustID
    FROM yourtable 
    WHERE ProdId = 300
)

NOT EXISTS:

SELECT CustID
FROM yourtable T1
WHERE ProdId = 105
AND NOT EXISTS
(
    SELECT NULL
    FROM yourtable T2
    WHERE T2.ProdId = 300
    AND T2.CustID = T1.CustID
)

.

SQL Server NOT IN NOT EXISTS:

SQL Server NOT EXISTS NOT IN , NULL. - -.

LEFT JOIN/IS NULL , , .

:

+5
SELECT CustID from T T1
WHERE  ProdID = 105
  AND  NOT EXISTS (SELECT 1 from T T2
                   WHERE  T2.ProdID = 300
                   AND    T2.CustID = T1.CustID)
+3

Several different approaches.

select CustID from t where ProdID=105
except
select CustID from t where ProdID=300

or

select CustID 
from t 
where ProdID in (105,300)
group by CustID 
having max(ProdID)=105
+1
source 
SELECT PPT.CustID FROM PreviousPurchasesTable PPT
WHERE PPT.ProdID = 105
AND PPT.CustID NOT IN (SELECT PPT2.CustID FROM PreviousPurchasesTable PPT2 WHERE PPT2.ProdID = 300)
0
source

Source: https://habr.com/ru/post/1764246/

More articles:


All Articles