I do not believe you can acheive the result you want. You would have to have entries for those company/city relationships in the CityCompany table - otherwise you're just pulling data out of thin air, and that is of no use ...

You could probably build a stored procedure to do it if you really really wanted to, but would the result it returned be valid? It wouldn't unless you had companies in those cities ... but then you'd have them listed in CityCompany and you could just build a SELECT to do it (with apropriate joins). Even though you could probably do this by building a SP for it, I wouldn't. I expect my databases to tell me the truth though and would find it very confusing if they did not. (You would be teaching it to "lie" if you build a SP to return the result you're after, and databases are typically used to protect the integrity of data)

Is that clear as mud?

HTH,

Eddie

----- Original Message -----
From: Thyago Almeida
To: firebird-support@yahoogroups.com
Sent: Tuesday, March 09, 2004 11:21 PM
Subject: [firebird-support] Query problem


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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.611 / Virus Database: 391 - Release Date: 3/6/2004

[Non-text portions of this message have been removed]