At 02:21 AM 10/03/2004 -0300, you wrote:

>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?
>

SELECT
C.CIT_NM_NAME,
CO.COM_NM_COMPANY,
CC.CCO_NM_EMAIL
FROM "City" C
LEFT JOIN "CityCompany" CC
ON C.CIT_ID = CC.CIT_ID
LEFT JOIN "Company"
ON CC.COM_ID = CO.COM_ID
/* and, if you want to eliminate the companies that are not
represented in a city .. */
WHERE CC.COM_ID IS NOT NULL

/heLen