Query with 2 right joins and group by doesn't display correct results

Can anybody help me with this query ?

I have 3 tables : orders, customers and products.

I need to have a list of the number of orders for each customer + for each product. Like this:

Customer A    Product X     4
Customer A    Product Y     0
Customer A    Product Z     0
Customer B    Product X     2
Customer B    Product Y     0
Customer B    Product Z     1
Customer C    Product X     0
Customer C    Product 开发者_如何学JAVAY     0
Customer C    Product Z     8

I tried a query like this :

SELECT c.Name, p.Name, COUNT(o.OrderID)
FROM orders AS o
RIGHT JOIN customers AS c ON c.CustomerID=o.CustomerID
RIGHT JOIN products AS p ON p.ProductID=o.ProductID
GROUP BY c.Name, p.Name

but I can't get it to work !

It only displays the combinations where the counter>0 (where there are records in 'orders'). But with only 1 join it DOES work, and then it DOES correctly display the records with a counter of 0. (In this example there are no products Y sold, but I do want to see Y in the list of combinations)

Any ideas?

Use a cross join. This is Oracle SQL, so not sure if it'll work for mysql.

select c.Name, p.Name, count(o.orderid)
from customers c cross join products p
left join orders o on c.customerid=o.CustomerID and p.ProductID=o.ProductID
GROUP BY c.Name, p.Name
order by c.name, p.name

You want to use outer joins and have the customers be the left side of the query, then the orders to be the left side of the remaining query, because it's the customers then orders you want to group by.

I prefer left outer joins because they map better to what you actually mean:

SELECT c.Name, p.Name, COUNT(o.OrderID)
FROM customers c
  left outer join orders o ON c.CustomerID=o.CustomerID
  left outer join products p ON p.ProductID=o.ProductID
GROUP BY c.Name, p.Name

select c.Name, p.Name, sum(case when o.ProductId is not null then 1 else 0 end)
from customers c,products p
left join orders o on c.CustomerID=o.CustomerID and p.ProductID=o.ProductID
GROUP BY c.Name, p.Name

If it still does not work you can do like this, which should be an equivalent

select c.Name, p.Name, sum(case when o.ProductId is not null then 1 else 0 end)
from customers c
join products p on 1=1
left join orders o on c.CustomerID=o.CustomerID and p.ProductID=o.ProductID
GROUP BY c.Name, p.Name

(I am guessing here .. as I said I have no sql at hand)