× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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?


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.