CODEWITHSUNDEEP
sqlpostgresql
simpatico
simpatico

Reputation: 11087

How to group by having the same id?

I want the customerid who bought product X and Y and Z, from the following schema:

Sales(customerid, productName, rid);

I could do the intersection:

select customerid from sales where productName='X' 
INTERSECT 
select customerid from sales where productName='X' 
INTERSTECT
select customerid from sales where productName='Z'

Is this the best I could do?

Upvotes: 1

Views: 290

Answers (2)

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

You could also select from sales 3 times:

select s1.customerID
from sales s1, sales s2, sales s3
where s1.productName = 'X'
    and S2.productName = 'Y'
    and S3.productName = 'Z'
    and (S1.customerID = S2.customerID
         and s2.customerID = s3.customerID);

Or rewrite using proper join syntax (might not be 100% though...)

select s1.customerID
from sales s1
inner join sales S2
    on s1.customerId = S2.customerID
inner join sales S3
    on s2.customerID = S3.customerId
where s1.productName = 'X'
    and S2.productName = 'Y'
    and S3.productName = 'Z';

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

Not sure if this works in postrgesql, but try:

select customerid 
from sales 
where productName in ('X', 'Y', 'Z')
group by customerid
having count(distinct productName) = 3

Upvotes: 6

Related Questions