Mensagem original - Helen Borrie em 10/3/2004 03:36 :

> At 02:21 AM 10/03/2004 -0300, you wrote:
>
> 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

Helen,

Using your query I obtain this result:

CIT_NM_NAME COM_NM_COMPANY CCO_NM_EMAIL
----------- -------------- ------------
City A Company 1 x@company1
City A Company 2 z@company2
City B Company 1 y@company1
City C NULL NULL

The result I really would like to obtain is:
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

I agree this is not a conventional result, but it would be useful for me.
However, I believe there is no "clean" syntax to obtain the result I
would like. Are there?

Thank you for your time and your help.

Thyago Almeida