SQL to show all customers and the items they have purchased without lots of joins-CodePudding

I would like to write a query that prints a result set whose rows are customers and the columns are the items with a 'Y' or 'N' in each field depending on whether the customer has purchased that item.

In short, I have the following tables:

CustomerID	CustomerName
1	Joe Bloggs
2	Jane Doe
3	John Smith

ItemID	ItemName
1	knife
2	fork
3	spoon

PurchaseID	CustomerID	ItemID
1	1	1
2	2	1
3	2	2
4	3	3

I would like to write a query that gives the following result:

Customer	Knife	Fork	Spoon
Joe Bloggs	Y	N	N
Jane Doe	Y	Y	N
John Smith	N	N	Y

I have written the following query which does the job

SELECT CustomerName, [appropriate case statements for the 'Y' and 'N']
FROM ct
LEFT JOIN pt AS pt1 ON ct.customerID = pt1.customerID
LEFT JOIN pt AS pt2 ON ct.customerID = pt2.customerID
LEFT JOIN pt AS pt3 ON ct.customerID = pt3.customerID
WHERE pt1.itemID = 1 AND pt2.itemID = 2 AND pt3.itemID = 3

The idea here is that I have to join another copy of the purchase table for each item. With a lot of items, this becomes extremely computationally costly.

I am wondering if there is any way I can get the same result without having to do a join for every item. Is there a way to do it with just one join? Any ideas are appreciated.

CodePudding user response：

You want conditional aggregation:

SELECT
    c.CustomerName AS Customer,
    CASE WHEN COUNT(CASE WHEN i.ItemName = 'knife' THEN 1 END) > 0
         THEN 'Y' ELSE 'N' END AS Knife,
    CASE WHEN COUNT(CASE WHEN i.ItemName = 'fork'  THEN 1 END) > 0
         THEN 'Y' ELSE 'N' END AS Fork,
    CASE WHEN COUNT(CASE WHEN i.ItemName = 'spoon' THEN 1 END) > 0
         THEN 'Y' ELSE 'N' END AS Spoon
FROM Customers c
LEFT JOIN CustomerItem ci
    ON ci.CustomerID = c.CustomerID
LEFT JOIN Items i
    ON i.ItemID = ci.ItemID
GROUP BY
    c.CustomerName;