At 04:48 AM 10/03/2004 -0300, you wrote:

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

I don't know what you mean by "clean". Your original description implied
that you stored some CityCompany records with null email addresses. What
you're now saying (if I understand you correctly) is that you want to have
a record for every possible combination of City and Company, even those for
which there is no intersection record?

If stored procedures are "dirty" then dirty is good for a requirement like
this.

/hb