Re: conditional join?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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


Sorry Chris and Dale, I thought I sent some examples yesterday but it seems
nothing got added to my first reply. I hope these samples help but I suspect
they probably won't.

Paul,

I have forgotten to send posts, or had them disappear, myself.

I see that you did not provide a description of your table structures, including indexes
and relationships. That will make it more difficult to give potential solutions.



I have been working out the issues in the native database language (this is
a Progress database I am linking to) though I am trying to create a front end
in Access for a special application. It is pretty complex in Progress, at
least the way I have it set up now, so I don't know how likely I am to be
able to achieve this in Access.

Just be aware, the sample data displayed below does not support your earlier description.

In your original post you wrote: "data is present in up to four heirarchically ordered
index fields."

None of these columns are in any form of hierarchy that I can determine just from looking
at the data.

A hierarchy in a relational database refers to various types of recursive relationships
(self-referencing cardinality), where a table as a foreign key pointing at another column
in itself. There doesn't appear to be anything like that here.



Here is a sample of the photo data:
photo.level1 photo.level2 photo.level3 photo.level4 photo.Id
21N 0 21N-2M
21N 3 D 21N-3M
21N 2 21N-31M
21N 26 F 2 21N-675M
21N 29 A 21N-804M
21N 152 G 9 21N-887M
21N 912 D 3 21N-3302M

Here is a sample of some provenience data:
prov.recId prov.level1 prov.level2 prov.level3 prov.level4 prov.Id
2 21N 3 0 21N1
3 21N 2 0 21N1
4 21N 26 0 21N24
5 21N 29 A 0 21N27
6 21N 152 0 21N17
7 21N 912 D 3 21N0




So, you want to find photo.Id values and prov.Id values based on matching
photo.level1-level4 with prov.level1-level4 (1-4, 1-3, 1-2, 1)? This does match up with
what you were saying earlier.


Here is an example of what I am trying to achieve:
photo.Id prov.Id
21N-2M

I don't understand. Why was no match found? The level1 values in both tables matched.


21N-3M 21N1
21N-31M 21N1
21N-675M 21N24
21N-804M 21N27
21N-887M 21N17
21N-3302M 21N0

The second photo record (21N-3M) has values in the first three levels and
matches the first prov record at the first two levels. Because there is no
prov record that matches to the third level the join is achieved on the
values of the first two levels.


How is this different from finding the same Id values based on matching only the level1
columns in both tables?

Is is that you want to excluce new attempts to match photo.Id with prov.Id with fewer
columns **once** a match has been located?

Hmm, that may be possible.


The first photo cannot be joined to prov because there are no prov
records that both match its level one value and do not have values
for lower levels.

This confuses me.

It appears to state:

WHERE photo.level1 = provenience.level1
AND photo.level2 <> provenience.level2
AND photo.level3 <> provenience.level3
AND photo.level4 <> provenience.level4

This does not match any of the other AND/OR logic that has been discussed so far.

I now have no certain idea what AND/OR logic is required.

Please provide exact AND/OR logic statements.


<snip>


Tables:

I guessed as best as I could on your table structures (again).


CREATE TABLE photo
(level1 TEXT(255)
,level2 TEXT(255)
,level3 TEXT(255)
,level4 TEXT(255)
,Id TEXT(255)
,CONSTRAINT pk_photo
PRIMARY KEY (level1
,level2
,level3
,level4)
)

CREATE TABLE provenience
(recId INTEGER
,level1 TEXT(255)
,level2 TEXT(255)
,level3 TEXT(255)
,level4 TEXT(255)
,Id TEXT(255)
,CONSTRAINT pk_provenience
PRIMARY KEY (recID)
,CONSTRAINT fk_provenience_photo_level1_level2_level3_level4
FOREIGN KEY (level1
,level2
,level3
,level4)
REFERENCES photo (level1
,level2
,level3
,level4)
)


Sample Data:

photo
level1, level2, level3, level4, Id
21N, 0, , , 21N-2M
21N, 3, D, , 21N-3M
21N, 2, , , 21N-31M
21N, 26, F, 2, 21N-675M
21N, 29, A, , 21N-804M
21N, 152, G, 9, 21N-887M
21N, 912, D, 3, 21N-3302M

provenience
recId, level1, level2, level3, level4, Id
2, 21N, 3, , 0, 21N1
3, 21N, 2, , 0, 21N1
4, 21N, 26, , 0, 21N24
5, 21N, 29, A, 0, 21N27
6, 21N, 152, , 0, 21N17
7, 21N, 912, D, 3, 21N0


I ran into a problem attempting to load this sample data (I saw it coming before I
actually attempted it).

The primary keys of the tables cannot contain null values, and because of this I could not
load the data.

I have guessed twice on your table structures, and was wrong on both occasions.

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.


.

Relevant Pages

  • Re: Wunderkindlein
    ... Spoke to Hazel at 18.57 and she sounded bright and breezy. ... total as her Chris went to the hospital at 3.45 (and they live less than ... law who have had all their babies in four hours or so from start to ... have a good photo from a mobile phone. ...
    (uk.media.radio.archers)
  • Re: SpodeFest Shirt Design Contest
    ... I like the #5 one with the relief map of the state, ... I think the actual photo on the shirt should be of Chris. ... but the one of him on the rock is very dramatic. ...
    (rec.motorcycles.dirt)
  • Re: Wunderkindlein
    ... Spoke to Hazel at 18.57 and she sounded bright and breezy. ... total as her Chris went to the hospital at 3.45 (and they live less than ... law who have had all their babies in four hours or so from start to ... have a good photo from a mobile phone. ...
    (uk.media.radio.archers)
  • Re: PM _ Archers news
    ... >> Chris J Dixon wrote: ... >> The photo was interesting though. ... >> pictured either brother with facial hair. ... > convinced Emma Grundy was blonde. ...
    (uk.media.radio.archers)
  • Re: Photo Id should equal to Member_ID
    ... primary key of Members table and foreign key of Photos table. ... If you want to limit 1 photo per member, ...
    (microsoft.public.access.forms)