João Daniel João Daniel - 5 months ago 12
SQL Question

Aggregate over every permutation of two tables

There are three tables:


  • customers

  • products

  • sales
    .



Every sales entry have a
customer_id
and
product_id
.

I need to
SELECT
and
SUM
the
sales.value
of each combination of
customers.id
and
products.id
. The point is: in the
sales
table, not every combination exists there, so I want that those combinations to have sum equal to zero in the results.

http://sqlfiddle.com/#!9/0f37a/2

This fiddle shows how the results should be, but I couldn't include lines for the combinations that doesn't exists and should sum 0. For example:

The combinations of customer_id 1 and product_id 1, 2 and 4 exists, not customer_id 1 and product_id 3. I want to include a line with this combination and sum(value) = 0.

EDIT

I have a table that contains every permutation of customer_id and product_id.

Answer

You could try doing a CROSS JOIN between the customers and products tables to get all combinations, and then join that result to sales:

SELECT c.id AS customer_id, p.id AS product_id,
    COALESCE(CAST(s.value AS CHAR(6)), 'NA') AS value
FROM customers c
INNER JOIN products p
LEFT JOIN sales s
    ON c.id = s.customer_id AND p.id = s.product_id
ORDER BY c.id, p.id

Note that a CROSS JOIN in MySQL is equivalent to doing an INNER JOIN with no ON criteria.

The link below is your Fiddle updated with the above query.

SQLFiddle