Hi,

> I'm having problems with a query in Firebird and I would be glad if
> someone could help me.
> I have three tables called, respectively, "Company", "City" and
> "CityCompany". In this third table I register the city ID, the company
> ID and the company contact's e-mail in a city.
> I would like to create a query like this: for each company, ALL the
> cities and the contact e-mails (NULL in case of abcense of a
> estabilished contact for that company in the city).

SELECT
CI.CIT_NM_NAME,
CO.COM_NM_COMPANY,
CC.CCO_NM_EMAIL
FROM
"Company" CO
JOIN "City" CI ON (1 = 1)
LEFT JOIN "CityCompany" CC ON
(CC.CIT_ID_CITY = CI.CIT_ID_CITY and
CC.COM_ID_COMPANY = CO.COM_ID_COMPANY)

Note that the result thus will be City-entries * Company-entries

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81