At 02:13 PM 5/02/2005 +1100, you wrote:

I take it you ported this database from Paradox?

What you'll need to do is restructure the Journey table a bit, since
Firebird uses foreign key constraints to establish relationships between
tables.

Paradox, OTOH, doesn't have built-in referential constraint checking and,
instead "enforces referential integrity" by merely comparing the physical
entries in primary indexes. If the PI of the dependent table doesn't
contain an element match for the PI of the "master" table, then RI is
considered broken.

These hierarchical keys are both a burden on design (propagation of keys
through many layers) and a problem of redundancy. You should abandon them
sooner, rather than later when they really start to bite you.

>As a beginner, the following problem defeats me, at least in the most
>efficient manner of resolution:
>
>I have built a query part of which is a cascaded join

Stop thinking about "cascaded" in terms of joins. It's a hierarchical
notion that you don't need with SQL and it gets confused with the SQL
meaning of "cascaded", which has to do with the optional update and delete
actions you can add to foreign key constraints to determine how children
behave when their parents leave home. :-)

>between three tables:
>
>The structure of that query (abbreviated):
>
>1. Journeys ( The master; approximately a total of 15000
>records from which to query 1 or more)
>primary key 'ID'
>plus other fields...Date, time,etc.
>____________________________________
>
>LEFT OUTER JOIN to:
>2. Routes (references Journeys.ID) - averaging 6 records per Journey -
>joined.
>Primary key: ID + Journey_ID (referenced by other tables)

Structure:

Needs to be Primary Key: ID

alter table Routes
drop constraint [whatever the name of the current PK constraint is];
commit;
alter table Routes
add constraint Pk_Routes primary key(ID);
add constraint Fk_Journeys foreign key (Journey_ID)
references Journeys;
commit;

>
>The problem fields:
>Departure_ID *
>Arrival_ID *

Why are the problems? Do you want them linked to the Primary Key of
Places? Let's assume so. You need two more foreign keys in Routes, then:

alter table Routes
add constraint Fk_Place_Depart (Departure_ID)
references Places,
add constraint Fl_Place_Arrive (Arrival_ID)
references Places;
commit;

>
>plus other fields...timings, distances, etc.
>_____________________________________
>
>LEFT OUTER JOIN(s) ? to
>3. Places
>Primary Key 'Place_ID'
>Placename
>plus other fields...descriptions
>_____________________________________
>
>The problem: How to reference Places.Place_ID from Routes.Departure_ID and
>Places.Place_ID from Routes.Arrival_ID
>
>* Wish-list: to have 2 x 1:1 joins between Routes and Places (i. -

Then do what is called a "re-entrant join".

>Departure_ID to Place_ID and ii. - Arrival_ID to PlaceID extracting
>Places.Placename for each.
>
>But, am I correct in saying that this is illegal?

Not illegal or legal - just mixed up conceptually.

>
>My solution: I currently use, temporarily, a simple look-up of
>Places.Placename and string type fields in 'Routes' instead of the Integer
>link field, but normalization states that this is not good practice and to
>me, a waste valuable resource space.

Not sure what you mean by a "lookup" but, if you are referring to a
correlated subquery used instead of the joins, it is sometimes a valid
thing to do to economise on a left outer join. It doesn't have anything to
do with normalisation but, for big queries, it can eat a lot.

Let's go with my assumptions about the relationships, and it is the
Journeys that you want to query, then you can go with something like this:

SELECT
J.ColA,
J.ColB,
J.whatever,
P1.Placename,
P2.Placename
from Journeys J
join Routes R on R.Journey_ID = J.ID
join Places P1 on R.Departure_ID = P1.Place_ID
join Places P2 on R.Arrival_ID = P2.Place_ID
where [aliased search columns in predicates]
order by [numbers or aliased ORDER BY columns]

I've used inner joins here 'cos I don't see what you would want with outer
joins (though there might be a requirement you haven't mentioned).

If this isn't like what you are after, come back with a tentative example
of what your current figuring has got for you. :-)

./heLen