Results 1 to 1 of 1
Apr 1, 2011, 14:15 #1
- Join Date
- Jul 2008
- 0 Post(s)
- 0 Thread(s)
Trying to get a count of all possibilities
I have 4 tables; country, line, platform, sku. The most important table is sku. It has the following fields:
Obviously line joins line, country joins country, platform joins platform. product and sku_type are only descriptors with 4 or 5 options each so I didn't bother making tables to join.
Essentially I am try to get a count of every possible combination so I can see where there are holes in the data. Given:
Countries = 18
Lines = 8
Platforms = 8
Products = 4
Sku_Type = 5
18 * 8 * 8 * 4 * 5 = 23040 lines should be returned.
My problem is that I'm not getting back the combination that do not have a sku. I'm stacking right joins in hopes that it would force the building of every column but I only see nulls for the list right join in the list. Here is what I have so far:
SELECT c.name AS Country, l.name AS Line, plat.name AS Platform, prod.unique_product AS Product, st.sku_type, count(*) AS Skus FROM `skus` s RIGHT JOIN (SELECT DISTINCT product AS unique_product FROM skus ) prod ON s.product = prod.unique_product RIGHT JOIN (SELECT DISTINCT sku_type FROM skus ) st ON s.product = prod.unique_product RIGHT JOIN `platforms` plat ON s.platform = plat.code ) RIGHT JOIN `lines` l ON s.line = l.label RIGHT JOIN `countries` c ON s.country = c.code ) GROUP BY c.code, l.label, plat.code, s.product, prod.unique_product, st.sku_type ORDER BY c.code, l.label, plat.code, prod.unique_product, st.sku_type
How can I create a Cartesian list from this?