Cross Join Query

Question

Rewording my original post for further clarification.

I current have the below tables:

Product_Ref

product_id
product_name

Products

product_id
so_date (date)
total_sales

Calendar

dt (date field, each row representing a single day for the past/next 10 years)

I am looking to produce a report that will tell me the number of products that were sold in the past 6 months (based on SYSDATE) on a daily basis, the report should be every combination of day in the last 6 months against every possible product_id in the format:

Product id | date | total sales

If I assume that there were 0 entries in the products table (i.e no sales) I would still expect a complete report output but instead it would show 6 months of zero'd data i.e.

1 | 2012-01-01 | 0
2 | 2012-01-01 | 0
3 | 2012-01-01 | 0
1 | 2012-01-02 | 0
2 | 2012-01-02 | 0
3 | 2012-01-02 | 0
…

This would assume there were 3 products in the product_reference table - my original query (noted below) was my starter for 10, but not sure where to go from here.

SELECT products.product_id, calendar.dt, products.total_sales
FROM products RIGHT JOIN calendar ON (products.so_date = calendar.dt)
WHERE calendar.dt < SYSDATE AND calendar.dt >= ADD_MONTHS(SYSDATE, -7)+1
ORDER BY calendar.dt ASC, products.product_id DESC;

Please add the data types. You are using the `Calendar.dt`-Column for both the join and the date operations. — Thomas Tschernich, Jan 16 '13 at 16:20
Also, we're assuming that `Calendar` is a standard 'calendar table', with basically a single row per day; among other things, that it has all the relevant data for the join (ie - it's not missing any days). Hmm, normally I'd be using a `LEFT JOIN` (with `Calendar` as the 'initial' table) for this, but the `RIGHT JOIN` version should still work... — Clockwork-Muse, Jan 16 '13 at 16:47

score 1 · Accepted Answer · edited May 23 '17 at 11:56

The clue is in the question - you are looking for a CROSS JOIN.

SELECT products.product_id, calendar.dt, products.total_sales
FROM Product_Ref
CROSS JOIN calendar 
LEFT JOIN products ON products.so_date = calendar.dt 
                    AND products.product_id = Product_Ref.product_id
WHERE calendar.dt < SYSDATE AND calendar.dt >= ADD_MONTHS(SYSDATE, -7)+1
ORDER BY calendar.dt ASC, products.product_id DESC;

I was confused at first by your table names where "Product" in fact means "sale" and "Product_Ref" is a product!

This is very similar to an example of the use of CROSS JOIN I once posted here.

sgeddes · Answer 2 · 2013-01-16T17:23:24.270

0

Assuming your desired output is to match only the products date with those in the calendar table, you should use an INNER JOIN:

SELECT c.dt, p.product_id, p.total_sales
FROM calendar c
INNER JOIN products p on c.dt = p.so_date
WHERE c.dt < SYSDATE and c.dt >= ADD_MONTHS(SYSDATE,-7)+1
ORDER BY c.dt ASC, p.product_id DESC;

A CROSS JOIN would produce results with every combination from your products table and your calendar table and thus not require the use of ON.

--EDIT

See edits below (UNTESTED):

SELECT PR.Product_ID, C.dt, P.TotalSales
FROM Calendar C 
   CROSS JOIN Product_Ref PR 
   LEFT JOIN Product P ON P.Product_Id = PR.Product_Id and p.so_date = c.dt
WHERE c.dt < SYSDATE and c.dt >= ADD_MONTHS(SYSDATE,-7)+1
ORDER BY c.dt ASC, p.product_id DESC;

Good luck.

edited Jan 16 '13 at 17:23

answered Jan 16 '13 at 16:23

sgeddes

62,311
6
61
83

That's actually exactly what I want (apologies for confusion) every combination of date (within the where range) and product_id. – ShowMeTellMe Jan 16 '13 at 16:30
Then your Right Join looks correct -- it will return every record in the calendar table (for that date range) with any matching product ids. If no product ids exist in that range, those values will be NULL but it will still return the dates in that range. What is your desired output and then it might be clearer. – sgeddes Jan 16 '13 at 16:47
I've updated my original question so there's less ambiguity and an example of the output. – ShowMeTellMe Jan 16 '13 at 17:04

score 0 · Answer 3 · answered Jan 16 '13 at 16:25

As far as I understood, what do you want is to have no result if there were no sales, write?

So, I think you just need to change the RIGHT JOIN to INNER JOIN.

By RIGHT joining, if there were register in the JOIN table and there weren't in the FROM table it will return the data from the JOIN table, with NULL values in the columns refering to the FROM table.

By INNER joining you will have results just if you there were data that match in both tables.

Hope I understood well and it helps.

Cross Join Query

3 Answers3