join 2 tables
lazarus / 16-Databases

----------------------------------------------------------------------
christensen · 2011-11-08 18:27

Hi i'm trying to join 2 tables and the result to display in one row
but only in 2 rows i can.

Here are the tables:

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

tb_city
- city_id
- city_name
- id_country(tb_country)

Code: [Select]select * from tb_client left outer join  tb_city on
tb_client.id_city_born=tb_city.city_id or
tb_client.id_city_died=tb_city.city_id the result is displayed in 2
fields.

Code: [Select]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=tb_city.city_id the first 'join'
work but in the second i get NULL fields.

----------------------------------------------------------------------
ludob · 2011-11-08 19:38

Quoteselect * 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=tb_city.city_id The last part
should be tb_client.id_city_died=city_name.city_id .  tb_city is
aliased to city_name. When you use tb_city it refers to the tb_city in
the first join.

----------------------------------------------------------------------
christensen · 2011-11-08 19:48

Many Thanks!  ... works like a charm