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).
So, supposing that I had, in the "City" table the following data:
CIT_ID_CITY CIT_NM_NAME
----------- -----------
1 City A
2 City B
3 City C

In the "Company" table the following data:
COM_ID_COMPANY COM_NM_COMPANY
-------------- --------------
1 Company 1
2 Company 2

And in the "CityCompany" table the following:
CIT_ID_CITY COM_ID_COMPANY CCO_NM_EMAIL
----------- -------------- ------------
1 1 x@company1
2 1 y@company1
1 2 z@company2

I would like to obtain this result:
CIT_NM_NAME COM_ID_COMPANY CCO_NM_EMAIL
----------- -------------- ------------
City A Company 1 x@company1
City B Company 1 y@company1
City C Company 1 NULL
City A Company 2 z@company2
City B Company 2 NULL
City C Company 2 NULL

Somebody can tell me which is the correct syntax to obtain this result?

Thanks in advance,

Thyago Almeida