Derive results if the result contains all values from another table

I am trying to get all the names of the customer who have purchase all the treatment packages (A101, A102, A103)

The following is an illustration of the tables I have:

Customer

CustID Surname Given Name
100    White   Layla
101    Wells   Mary

Account

AcctNo CustID
600    100
601    101

AccountLine

AcctNo TreatmentNo
600    A102
601    A103
600    A102
600    A101
601    A101

Treatment

TreatmentNo Description
A101        For face
A102        For body
A103        For legs

In my statement, I wrote it as:

SELECT c.givenname
FROM accountline al, treatment t, account a, customer c
WHERE al.treatmentno IN (SELECT treatmentno FROM treatment)
AND al.accountno IN (SELECT accountno FROM account)
AND c.custid IN (SELECT custid FROM customer)
GROUPBY c.givenname

The results that I derived from the above statement has instead return me customer who have purchase any of the treatment packages. Help, please?

P.S – I cannot display both the given name and family name together (SELECT c.givenname, c.familyname in my first line) as this results in an error stating not a GROUP BY expression, why is that so?

Answer

You can try the below – use a having clause to check who’ve purchased all three products

SELECT c.givenname,c.familyname
FROM accountline al join treatment t on al.TreatmentNo=t.TreatmentNo
join account a on a.AcctNo=al.AcctNo
join customer c on c.CustID=a.CustID
where t.TreatmentNo in ('A101', 'A102', 'A103')
GROUP BY c.givenname,c.familyname
having count(distinct t.TreatmentNo)=3

Advertisement

Answer