Re: conditional join?

Thanks for the lead Chris, that sounds promising. I will have to look into
the EXISTS condition syntax when I get back after the weekend. I have added
the information about the correspondence between the hypothetical "level"
fields and the actual fields, and have identified the keys. See below.
Multi-field keys are very common in our database, not so common in Access
databases I believe.

I am just about to head off for the weekend so have a good one and we'll
connect later.

Paul

"Chris2" wrote:


"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:148C59C9-1EFA-4E77-944E-E7D20C199C25@xxxxxxxxxxxxxxxx

"Chris2" wrote:


"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D49F1267-7714-487A-B154-1FE4099E44AD@xxxxxxxxxxxxxxxx

"Chris2" wrote:


"Bart" <Bart@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8DCDDF8B-1F08-477A-8782-0A9FAD40663C@xxxxxxxxxxxxxxxx
Hi Chris,


<snip>


Sincerely,

Chris O.


<snip>


Can you please post your table structures, including indexes and relationships?

I have some ideas about to do this, but until I can build a pair of tables that match
yours that are loaded with the data, I will have no way to find out if they will work.


Sincerely,

Chris O.



<snip>

I'm not sure how much information you want but I will supply the SQL
translation of the Progress schema for a number of tables below.

Ok, two things are still missing.

I need a mini-chart that translates the previously supplied level1-4 column names into the
real column names so that I know what sample data goes where.

photo.level1 = photo.siteNum + photo.siteCode
photo.level2 = photo.opCode
photo.level3 = photo.sopCode
photo.level4 = photo.lotNum

prov.level1 = prov.siteNum + prov.siteCode
prov.level2 = prov.opCode
prov.level3 = prov.sopCode
prov.level4 = prov.lotNum


No primary keys or foreign keys were shown. Please add those declarations (just those, no
need to repost the whole set of table descriptions).

The crmProv table doesn't really have a primary key (unique index) though
all of its fields are indexed and are foreign keys.

The primary key(s) for the photo table are (collectively) siteNum, siteCode,
filmType, photoNumber and regionCode (though regionCode is really a non-issue
as it is always the same in our database). The foreign keys in photo are
siteNum, siteCode, opCode, sopCode, and lotNumber.

The site table has two primary keys siteNum and siteCode and one foreign key
parkCode.
The operation table has three primary keys, siteNum, siteCode and opCode and
no foreign keys.
The subOper table has four primary keys, siteNum, siteCode, opCode and
sopCode and no foreign keys.
The lot table has five primary keys, siteNum, siteCode, opCode, sopCode and
lotNumber and no foreign keys.




Note for future consideration: When supplying DDL SQL, non-relevant columns (those that
aren't going to be used in the query/process, or those that do not describe primary or
foreign keys), may be omitted in a newsgroup posting.



Sorry. I will watch that next time.



I will include the Progress procedure I am using to achieve the results.
While you may not be familiar with Progress the language is quite easy to
understand and you should probably be able figure out more easily what it is
I am after from reading the code. It may not be the best implementation but
it is working.

I am glad it is working. While I can guess about what some of the Progress code does,
guessing isn't what you need and me doing that won't do you any good. (I would just be
stumbling through one interpretation after another, with you correcting me most of the
time.)



In SQL is it possible to include some conditional statements within Select
(e.g. IIF, CASE) to determine the level of provenience recorded in the photo
record and to direct the join to one of four instances of the crmProv table
each with different join critera?


I suppose you could try to use IIF and SWITCH, but I wasn't planning on that.

I was going to try querying for all 4-column matches, and then doing a query for all
3-column matches where NOT EXISTS all the 4-column matches, and then doing a query for all
2-column matches where NOT EXISTS all the 4-column and 3-column matches, etc.

Does that even remotely sound like what you are trying to achieve? (It does to me, but
that doesn't mean I am right.)


I think that does sound like it could work, and may be just what I was
looking for. I will take a closer look at that. Thanks, Paul.


Sincerely,

Chris O.



.

Relevant Pages

  • Re: Table Quandry
    ... Heres what I have.....They are all Primary Keys escept the last 2 fields.... ... Its main purpose should be to provide a value identifying the record so that you can use that same value in other Tables (as Foreign Keys) to refer to this record. ... the matching record is supposed to be, ... I usually link them by specifying Lookup properties in foreign-key ...
    (microsoft.public.access.gettingstarted)
  • Re: Table Quandry
    ... Well, one suggestion I have is to get rid of all the Primary Keys except one, and that one I suggest hiding in Table Datasheet View so that you don't see it. ... Its main purpose should be to provide a value identifying the record so that you can use that same value in other Tables (as Foreign Keys) to refer to this record. ... They won't automatically match up. ... I usually link them by specifying Lookup properties in foreign-key ...
    (microsoft.public.access.gettingstarted)
  • Primary Keys and Valid_From / Valid_To
    ... I have basic questions on how one best organizes primary keys (and ... also foreign keys) and data integrity in an enviroment where one has ... t_prices on article_id and currency. ... exactly one price / currency for each entry in t_orders. ...
    (comp.databases.oracle.misc)
  • Pg v Mysql & primary_key_info() & foreign_key_info()
    ... At http://savage.net.au/Perl/list-indexes.pl you can download a demo program. ... Give the program 1 table name of an existing database on the command line, ... Primary keys found: 1 ... Foreign keys found: 4 ...
    (perl.dbi.users)