RE: [EXTERNAL] Re: SQL to join tables without having a driver file

I was thinking along the same lines
Except using union - instead of union all
Don’t know which would be more efficient/quicker

with itemstore as (
select distinct item,store
from file1
union
select distinct item,store
from file2
union
select distinct item,store
from file3
)
select item,store from itemstore


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
‘If you're going through hell, keep going.’
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Peter Dow
Sent: Monday, October 19, 2020 3:02 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: SQL to join tables without having a driver file

I don't know if it's what your want or if it's better, but how about

with itemstore as (
select distinct item,store
  from file1
union all
select distinct item,store
  from file2
union all
select distinct item,store
  from file3
)
select distinct item,store from itemstore

It assumes that the item and store in files 1, 2, & 3 have already been validated to be in the master files.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx> pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>


/
On 10/19/2020 11:05 AM, 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