|
Not really following your key structure...
Generally, you don't want to join a detail to a detail...aggregate the
details separately then join the aggregates.
It's easy to build your "driver file" on the fly...
-- build itemStore file
with itemStore as (select item, store
from item cross join store)
, dtl1 as (select item, store, sum(something) as tot1
from detail1
group by item, store)
, dtl2 as (select item, store, sum(something2) as tot2
from detail2
group by item, store)
, dtl3 as (select item, store, sum(something3) as tot3
from detail3
group by item, store)
select h.item, h.store, tot1, tot2, tot3
from itemSTore
left join dtl1 on ...
left join dtl2 on ...
left join dtl3 on ...
If you're not interested in any rows that don't exist in at least one
detail, then perhaps FULL OUTER JOIN may be useful
with dtl1 as (select item, store, sum(something) as tot1
from detail1
group by item, store)
, dtl2 as (select item, store, sum(something2) as tot2
from detail2
group by item, store)
, dtl3 as (select item, store, sum(something3) as tot3
from detail3
group by item, store)
select coalesce(d1.item, d2.item, d3.item) as item
, coalesce(d1.store, d2.store, d3.store) as store
, tot1, tot2, tot3
from itemSTore
left join dtl1 D1on ...
left join dtl2 D2 on ...
left join dtl3 D3 on ...
Charles
On Mon, Oct 19, 2020 at 12:06 PM <smith5646midrange@xxxxxxxxx> wrote:
I'm having trouble figuring out how to join some files because I don't
really have a driver file and I'm looking for help.
I have two master files, one contains all of the items and the other
contains all of the stores. I don't have a file that contains all
item/store combinations.
I have three detail files that contain miscellaneous item/store level
information. Each item/store combination could exist in 3 of the files, 2
of the files, 1 of the files, or it might not exist in any of these files.
I only want to select the items that exist in at least one of the three
files and summarize/merge the data into a single file (used later for
other
reporting). Because File1 could have store/items not in File2 and File2
could have store/items not in File1, and the same goes for File3, I don't
really have a driver file.
I have considered doing the following but it seems there should be a
better
way to do this.
with itemAndStore as (
select item#, store
from itemfile
join storefile
on store <> 0
<--apparently there has to be an "on clause" so this meets that
requirement
and includes all stores
)
select *
from itemAndStore
left join File1
on File1.store = itemAndStore.store
and File1.item# = itemAndStore.item#
left join File2
on File2.store = itemAndStore.store
and File2.item# = itemAndStore.item#
left join File3
on File3.store = itemAndStore.store
and File3.item# = itemAndStore.item#
where File1.item# is not null
or File2.item# is not null
or File3.item# is not null
Is there a better way to do this?
Is this going to cause me a problem that I have not yet stumbled over?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
