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?