At 01:31 PM 01-02-02 +0000, you wrote:

>> You have a view joining Persons and Zipcodes
>> then you join that view back to a dependent of the Persons table
>> and join again into the Zipcodes table...
>
>mmm, this is my query:
>
>SELECT blabla
>FROM APPOINTMENTS A
>LEFT OUTER JOIN VIEW_PERSONS P ON A.PERSON_ID=P.PERSON_ID
>
>what do you mean with 'join again into the ZipCodes table?'
>
>> I should think the optimizer gave up and went to the pub.
>>
>> Is there any reason why your view can't join all three tables, or
>indeed why you can't just get this dataset with a plain SQL
>statement, something like
>>
>> select p.personid, a.something, ... z.cityname, z.zipcode
>> from appointments a
>> join persons p on a.personid = p.personid
>> join zipcodes z on p.zipcode=z.zipcode
>> where <identified column> = ....
>
>yes, I see, beautiful.
>Now I've another problem (probably not that big for the gurus). In
>the appointments table is another ID, named service_id (this is the
>key to a service table, place where the person has to go). This
>service table has also a zipcode_id.
>How can I create a view which combines all this information:
>
>The following query returns a result, but is this a correct query I'm
>doing? I didn't new you could use several joins with the same table
>(Postcodes)
>
>select blabla
>from appointments a
>join persons p on a.personid = p.personid
>join zipcodes z1 on p.zipcode=z1.zipcode
>join services s on a.serviceid = s.serviceid
>join zipcodes z2 on s.zipcode=z2.zipcode
>
>do joins have a certain order in which they calculate?

Well...ummm...yes, they do...it is called a Query Plan. The optimizer makes one based on the indexes it can use. (You can make your own Query Plans but they don't survive very well and you need to be very au fait with SQL and the ways different kinds of indexes work, to make them useful.)

If you're talking the column order of the output, it is determined by the order in the select statement, not the order in which the join statements are submitted...

>I used left outer join in my own examples. eg :
>
>select blabla
>from appointments a
>left outer join persons p on a.personid = p.personid
>join zipcodes z1 on p.zipcode=z1.zipcode
>left outer join services s on a.serviceid = s.serviceid
>join zipcodes z2 on s.zipcode=z2.zipcode
>
>Is this better?

No, unless you have appointments records for which no personid exists. But I think you said you had a foreign key in appointments referring to persons, which would mean that no such records could exist. The same applies to the serviceid - if it is a foreign key, there won't be any appointments records with no serviceid, so left outer join is absurd.

But the re-entrant join on zipcodes is fine.

I'm still curious about why you need to use views for these...what is your client application environment?

H.

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________