Subject Re: [firebird-support] Multiple Joins to the same field
Author Helen Borrie
At 01:01 AM 2/11/2005 +0000, you wrote:
>I have a customers table that lists four different phone numbers and
>four different types. For example, phone_type_ID1, phone_type_ID2,
>etc. All of these need to join to the same PhoneNumberType table. This
>sounds easy, but I've been struggling with the syntax. What's the
>right way to do this?

Use multiple aliases for the PhoneNumberType table.

pnt1.description as desc1, cust.PN1,
pnt2.description as desc2, cust.PN2,
pnt3.description as desc3, cust.PN3,
pnt4.description as desc4, cust.PN4
from customer cust
join PhoneNumberType pnt1 on pnt1.ID=cust.phone_type_ID1
join PhoneNumberType pnt2 on pnt2.ID=cust.phone_type_ID2
join PhoneNumberType pnt3 on pnt3.ID=cust.phone_type_ID3
join PhoneNumberType pnt4 on pnt4.ID=cust.phone_type_ID4

It seems your customer table is crying out for normalization!!