|
I'm still fighting with this. I have never done a full outer join with
more
than two files but now I have three.
Can someone give me some additional details on the full outer join? Using
the example provided below
from dtl1 D1
full outer join dtl2 D2 on ...
full outer join dtl3 D3 on ...
How do I build the join for D3 when the record I'm matching on could be in
D1 or D2 but not necessarily in both? Is it
from dtl1 D1
full outer join dtl2 D2 on ...
full outer join dtl3 D3
on (d3.item = d1.item or d3.item = d2.item)
and (d3.store = d1.store or d3.store = d2.store)
If so, then I have something else really hosed up in my program or what I
think the data looks like because this doesn't seem to be working.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Monday, October 19, 2020 4:26 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL to join tables without having a driver file
Sorry...hit send too soon..that second example should be...
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 dtl1 D1
full outer join dtl2 D2 on ...
full outer join dtl3 D3 on ...
Charles
On Mon, Oct 19, 2020 at 2:14 PM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:
Not really following your key structure...these files.
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
--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
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
--
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
