Mensagem original - Arno Brinkman em 10/3/2004 06:25 :

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

Arno,

Thank you, it works. :-) And don't worry, the "City / Company" was
just an example, I will use this just in very small tables.
I was trying something like this:

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

But this is not a valid query and then I get the error: "The cursor
identified in the update or delete statement is not positioned on a row.
No current record for fetch operation.". Thank you for show me the
correct syntax.
I would like to thank Helen and Eddie for help.

Thyago Almeida