SQL Conversion from Sybase

[dtrout]

I have the following SQL statement from a Sybase 5 database and I don't know how to get it to work in a SQL 7 database. Any help much appreciated.

TIA,
DBA without the PAY

SELECT firms.id,
firms.description,
firm_mailing_addresses.firm,
firm_mailing_addresses.address1,
firm_mailing_addresses.address2,
zip_codes_a.city as mailing_city,
zip_codes_a.state as mailing_state,
firm_mailing_addresses.zip_code as mailing_zip,
firm_mailing_addresses.zip_plus4 as mailing_zip_plus4,
firm_physical_addresses.firm,
firm_physical_addresses.directory_phone,
firm_physical_addresses.address1,
zip_codes_b.city as physical_city,
zip_codes_b.state as physical_state,
firm_physical_addresses.zip_code as physical_zip,
firm_physical_addresses.zip_plus4 as physical_zip_plus4
FROM {oj {oj firms LEFT OUTER JOIN firm_physical_addresses
ON firms.id = firm_physical_addresses.firm}
LEFT OUTER JOIN firm_mailing_addresses
ON firms.id = firm_mailing_addresses.firm},
{oj firm_physical_addresses LEFT OUTER JOIN zip_codes zip_codes_b
ON firm_physical_addresses.zip_code = zip_codes_b.zip_code
AND firm_physical_addresses.zip_plus4 = zip_codes_b.zip_plus4},
{oj firm_mailing_addresses LEFT OUTER JOIN zip_codes zip_codes_a
ON firm_mailing_addresses.zip_code = zip_codes_a.zip_code
AND firm_mailing_addresses.zip_plus4 = zip_codes_a.zip_plus4}
WHERE firms.id = 120

[MAK]

Try this

SELECT firms.id, firms.description, firm_mailing_addresses.firm, firm_mailing_addresses.address1,
firm_mailing_addresses.address2, zip_codes_a.city as mailing_city, zip_codes_a.state as mailing_state,
firm_mailing_addresses.zip_code as mailing_zip, firm_mailing_addresses.zip_plus4 as mailing_zip_plus4, firm_physical_addresses.firm,
firm_physical_addresses.directory_phone, firm_physical_addresses.address1, zip_codes_b.city as physical_city,
zip_codes_b.state as physical_state, firm_physical_addresses.zip_code as physical_zip, firm_physical_addresses.zip_plus4 as physical_zip_plus4
FROM
firms LEFT OUTER JOIN firm_physical_addresses
ON firms.id = firm_physical_addresses.firm
LEFT OUTER JOIN firm_mailing_addresses
ON firms.id = firm_mailing_addresses.firm,
LEFT OUTER JOIN zip_codes zip_codes_b
ON firm_physical_addresses.zip_code = zip_codes_b.zip_code
LEFT OUTER JOIN zip_codes zip_codes_a
ON firm_mailing_addresses.zip_code = zip_codes_a.zip_code
WHERE firms.id = 120
and firm_physical_addresses.zip_plus4 = zip_codes_b.zip_plus4
and firm_mailing_addresses.zip_plus4 = zip_codes_a.zip_plus4

[dtrout]

Thanks, but no go! Incorrect syntax near the keyword 'LEFT'.

[MAK]

Try now

SELECT firms.id, firms.description, firm_mailing_addresses.firm, firm_mailing_addresses.address1,
firm_mailing_addresses.address2, zip_codes_a.city as mailing_city, zip_codes_a.state as mailing_state,
firm_mailing_addresses.zip_code as mailing_zip, firm_mailing_addresses.zip_plus4 as mailing_zip_plus4, firm_physical_addresses.firm,
firm_physical_addresses.directory_phone, firm_physical_addresses.address1, zip_codes_b.city as physical_city,
zip_codes_b.state as physical_state, firm_physical_addresses.zip_code as physical_zip, firm_physical_addresses.zip_plus4 as physical_zip_plus4
FROM
firms LEFT OUTER JOIN firm_physical_addresses
ON firms.id = firm_physical_addresses.firm
LEFT OUTER JOIN firm_mailing_addresses
ON firms.id = firm_mailing_addresses.firm
LEFT OUTER JOIN zip_codes zip_codes_b
ON firm_physical_addresses.zip_code = zip_codes_b.zip_code
LEFT OUTER JOIN zip_codes zip_codes_a
ON firm_mailing_addresses.zip_code = zip_codes_a.zip_code
WHERE firms.id = 120
and firm_physical_addresses.zip_plus4 = zip_codes_b.zip_plus4
and firm_mailing_addresses.zip_plus4 = zip_codes_a.zip_plus4

[MAK]

and check whether both results are same