I've tried hunting for this, but maybe I'm just wording it wrong.
Can anyone help with trying to count records, regardless of whether a match exists or not?
In this, I have a table showing returns over a given period.
I have a pre-defined listing of products and manufacturers (any manufacturer can produce any of the products given)
(Example, Hard Drive can be produced by Samsung, Maxtor, etc...)
What I am looking to do is get an output that shows ALL products, by all potential manufacturers that MAY have been returned under a given type (if there were no returns, I still want to see that combination as a "0" element).
EXCHANGE UPGRADE BUYER REMORSE
Hard Drive SAMSUNG 0 5 1
Hard Drive MAXTOR 3 0 0
Hard Drive SEAGATE 0 0 0
As it sits, I am running this in a looping process where I first build a "SUM(CASE WHEN" block for the "return types".
I then get the "product types" and looping through that,
Get each "manufacturer" and then run a 1 line at a time SQL that looks like:
SELECT PRODUCT, MANUFACTURER, SUM(CASE WHEN PRODUCT=x aND MANU=y THEN 1 else 0 END) as "EXCHANGE"
This seems extremely ineffecient in that if I have 5 products and 5 manufacturers, I am running 25 different SQL statemetns to build my results (results are output througha web page, so I build one line at a time, tack it onto the previous results, then continue).
My thinking is that there must be a way to do something like left outer join on the products->Manufacturers->Returns to get this same info (get ALL products, regardless of whether there is a manufacturer, regardless of whether there is a return or not).
The "sum case" is the easiest part, but I'm totally lost on getting the left-side items.
HELP!!!!! (and if this info is out there, please let me know what conditions you used to search - I've been hunting for the last 2 days and my brain is frazzled).