Nested Selects, Outer Joins 1

[bradsta]

Hello,

I am trying to export a file from 2 tables in a database, called Centres and Centres_Contactinfo. The Centres table has most of the details such as address etc, but the Centres_Contactinfo table contains the phone numbers.

They are linked on a Centre_Id field in both tables. Each centre record can have up to 2 records in the contactinfo table but can also have none. I need all records from the Centres table regardless of whether they have a record in the Contactinfo table.

Aside from the foreign and primary keys in the Contactinfo table the only fields are Type and Info. Type contains the type of number (eg. Main Phone or Main Fax) and Info contains the number.

I have a query as follows:

select *
from
(
select *
from centres_contactinfo
where centres_contactinfo.type = 'Main Phone'
) as MainPhone

right outer join centres
on MainPhone.centre_id = centres.centre_id and

(
select *
from centres_contactinfo
where centres_contactinfo.type = 'Main Fax'
) as MainFax

right outer join centres
on MainFax.centre_id = centres.centre_id


When I parse the query it says there is an error near the "as MainFax". It works fine at getting all centres when I stop after the Main Phone section.

How do I join the 2 nested selects to be part of the main query, or is there a better way to do it.

Thanks,
Brad

 

[swampBoogie]

select centres.*, ph.*,fx.*
from centres left outer join centres_contactinfo ph
on ph.centre_id = centres.centre_id
and ph.type = 'Main Phone'
left outer join centres_contactinfo fx
on fx.centre_id = centres.centre_id
and fx.type = 'Main Fax'