Multi table query

Forum related to Firebird

Moderators: gto, cipto_kh, EgonHugeist

christensen
Fresh Boarder
Fresh Boarder
Posts: 12
Joined: 13.05.2010, 11:31

Multi table query

Postby christensen » 05.11.2011, 16:50

Hi,
I'm working on a small project but i have problems querying multiple tables.
Here are my tables:

Tb_clients
- client_id
- client_name
- client_phone
- id_city_born(tb_city)
- id_city_died(tb_city)

tb_city
- city_id
- city_name
- id_country(tb_country)

tb_country
- country_id
- country_name

Now i want to get the whole table content:
- client_name
- client_phone
- city_name/country_name(born)
- city_name/country_name(died)

I want to return those fields in one row of the grid, but unfortunately with no luck :( the return is in 2 rows.

Code: Select all

select * from (tb_client join  tb_city on tb_client.id_city_born=tb_city.city_id or tb_client.id_city_died=tb_city.city_id) join tb_country on tb_country.country_id=tb_city.country_id

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 08.11.2011, 22:46

Hi,

You must join the client table twice to the cities table and eache joined city table should be joined to one country table.
I don't have your schema in my database, but using oracle syntax this would be

Code: Select all

select c.client_name, c.client_phone,cb.country_name,cd.country_name
from tb_clients c,tb_city ctb,tb_city ctd,tb_country cb,tb_country cd
where c.id_city_born = ctb.city_id
  and c.id_city_died = ctd.city_id
  and ctb.id_country = cb.country_id
  and ctd.id_country = cd.country_id

Attention !!!
- This code is not tested literally, so typographical errors may be present
- This is oracle syntax. Translation to ANSI SQL is your job
- This code doesn't take into account c.id_city_born and id_city_died may be null. Actually, if records where one of these fields is null should be shown you'll need a substantially different query. The city-country join will probably need to be moved to a subquery.
Conclusion: you'll probably need to study join and left join syntax for your database to make sure this works exactly as you need. I'd say: test with city names first, and then try to change these into county names. And don't forget to test for the null cases...

Mark
Image

christensen
Fresh Boarder
Fresh Boarder
Posts: 12
Joined: 13.05.2010, 11:31

Postby christensen » 09.11.2011, 15:33

thanks mdaems,

i solved it in other way.. with alias. "city_name" for the second "tb_city" query.

Code: Select all

select * from tb_client left outer join  tb_city on tb_client.id_city_born=tb_city.city_id left outer  join tb_city city_name on tb_client.id_city_died=city_name.city_id

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 09.11.2011, 21:17

Hey, this query isn't complete! You don't have the country names ;)

Well, actually this query is exactly what I meant by
test with city names first
and
if records where one of these fields is null should be shown you'll need a substantially different query


Happy querying!
Image


Return to “Firebird”

Who is online

Users browsing this forum: No registered users and 1 guest