Combine four table fields into one record

[Chris Lutka]

I've been racking my brains all day over this. And I'm not the best
at SQL either.

I need a query that will produce the following results:
Product,Warehou se,Sold_LastYea r,Sold_ThisYear ,Sold_3Months,S old_MTD

I've got four queries to give me the Sold_* fields. Their fields are
Product, Warehouse, and Units. I also have a table which these
queries originated from. The table UV_SPPROD (I didn't name it) has
Product, Warehouse, Period (aka date), and Units. Basically, the four
queries remove the date and give me the unit sum for a given date
range. Those queries all work fine.

My trouble is that when trying to pull in all the data, I can't seem
to match the product and warehouses for the four queries together to
get me a combined output. Just to make things more difficult, not all
periods are listed for each product/warehouse combination meaning that
I cannot simply join all product fields and all warehouse fields
together. I may "lose" data. At least that's what my tests showed.

I've tried using the base table UV_SPPROD to perform a left join but
seem to get cartesian joins with the data or lost data. I've also
tried a union with zeros as placeholders. That will work if I create
a final query to sum based on product and warehouse. I'm hoping to
create one query that will write the information.

Any help is appreciated.

-Chris

[Bas Cost Budde]

Re: Combine four table fields into one record

I feel you are close. Left joins are what I would use in this case,
anyway. The "source table" (on that left side) should include all
possible combinations, maybe that requires an extra query.

The query should have five source objects, all of them probably queries
themselves. Four are joined "with arrow". Right?

You have arrived correctly at the observation that you cannot use simple
joins. Maybe there is a workaround in a crosstab query (produce a result
set that calculates your columns and adds a label, then do the crosstab
-- nah, forget it, way too difficult)

If you need that extra last query, do it. Have a result first, trouble
yourself for speedups later.

--
Bas Cost Budde

http://www.heuveltop.org/BasCB

but the domain is nl