SQL Query to Summarize Three Tables [duplicate]

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!

Yogesh Sharma · Accepted Answer · 2018-06-14 18:13:24Z

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;

answered Jun 14, 2018 at 18:13

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aura · Accepted Answer · 2018-06-15 13:22:24Z

0

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.

edited Jun 15, 2018 at 13:22

Aura

1,3032 gold badges18 silver badges31 bronze badges

answered Jun 14, 2018 at 18:11

Samsimus12

285 bronze badges

Collectives™ on Stack Overflow

SQL Query to Summarize Three Tables [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related