João Daniel João Daniel - 1 year ago 128
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download