RE: SQL to join tables without having a driver file

Is "cross join" the way to join two files that have no common fields and it
basically appends the two record formats together?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Monday, October 19, 2020 4:15 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL to join tables without having a driver file

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

--
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