SQL Query to Summarize Three Tables

Question

Let's say i have a database with the following structure:

Customers  
ID Name  
1  Bob  
2  Jim  
3  Sally  

Products  
ID Description  
10 Pencil  
11 Ruler  
12 Eraser  

Purchases  
ID Customer_ID Product_ID  
1 1 10  
2 1 11    
3 2 10  
4 3 12 

Is it possible (without doing a for each customer, for each product) to write a query to get a format like:

Customer Pencil Ruler Eraser  
1 1 1 0  
2 1 0 0  
3 0 0 1  

Here's where I got, but I would like to avoid this structure since it will get lengthy and the number of products will change. Also I am getting duplicate rows when the customer indeed purchased product 10:

  SELECT DISTINCT CUSTOMER.CUSTOMER_ID, (case when a.CUSTOMER_ID = 
    CUSTOMER.CUSTOMER_ID THEN 1 ELSE 0 END) as product10
    from 
    (SELECT PURCHASES.CUSTOMER_ID from PURCHASES
    WHERE PURCHASES.PRODUCT_ID = 10) a, CUSTOMER

CUSTOMER_ID product10  
1 1  
1 0  
2 1  
2 0  
3 0  

Thanks!

Answer 1

You can do conditional aggregation :

select c.id, sum(case when p.Description = 'Pencil' then 1 else 0 end) as Pencil,
             sum(case when p.Description = 'Ruler' then 1 else 0 end) as Ruler,
             sum(case when p.Description = 'Eraser' then 1 else 0 end) as Eraser
from Purchases prc inner join 
     Customers c
     on c.id = prc.Customer_ID inner join
     Products p 
     on p.id = prc.Product_ID  
group by c.id;

Answer 2

You could try this:

SELECT DISTINCT t1.ID, t3.ID 
FROM Customers t1 
INNER JOIN Purchases t2 
   ON t1.ID = t2.Customer_ID 
INNER JOIN Products t3 
   ON t2.Product_ID = t3.ID;

It won't return everything a customer has in one row but it will return all rows where the relations between customer and product exist without duplicates.