Multi table query

[christensen]

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

[mdaems]

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

[christensen]

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

[mdaems]

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!