how to make two joins to one table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to make two joins to one table

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2011-02-14 : 17:25:04

i have the following two tables for countries and cities :
create table Countries(
Country_Id int not null auto_increment,
Constraint PK_Cou_Id primary key (Country_Id),
Country varchar(30)not null
)
create table Cities(
City_Id int not null auto_increment,
constraint PK_City_Id primary key (City_Id),
Country_Id int not null,
constraint FK_Cou_Id foreign key (Country_Id) references Countries(Country_Id),
City varchar(30)not null
)
and i have a clients table each client has a city and a resident country:
create table clients(
client_id int not null,
client_name varchar(50)not null,
City_Id int not null ,
constraint FK_City_Id foreign key (City_Id) references Cities(City_Id),
Resident_Id int not null,
constraint FK_Cou1_Id foreign key (Resident_Id) references Countries(Country_Id),
)
countries
country_id country
1 USA
2 Germany

cities
city_id country_id city
1 1 clafornia
2 2 Munich

clients
client_id client_name city_id resident_id
1 "michael" 1 2
2 "john" 2 1

so how can i write a sql query with two joins to the countries table to get the data as the following:

client_name Country Resident_Country
"michael" USA Germany
"john" Germany USA
select client_name,country,country as 'Resident_Country' from clients
inner join countries on clients.resident_id = countries.country_id and countries.city_id = clients.city_id

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 17:59:39

You can just specify another join..


Declare @countries table  
(country_id int not null,
 country varchar(20) not null)
INSERT INTO @countries
SELECT 1 ,'USA' UNION
SELECT 2, 'Germany'

DECLARE @cities table (
 city_id int not null
,country_id int not null
,city varchar(25) not null )
INSERT @cities
SELECT 1 ,1 ,'california' UNION
SELECT 2, 2, 'Munich'

DECLARE @clients TABLE (
client_id int not null,
client_name varchar(20) not null,
city_id int not null,
resident_id int not null)
INSERT @clients
SELECT 1 ,'michael', 1, 2 UNION
SELECT 2 ,'john' ,2, 1

SELECT client_id
, client_name,city
,t.country as resident_country
,t2.country as country
FROM @clients c inner join @cities s on c.city_id = s.city_id
				inner join @countries t on c.city_id = t.country_id
				inner join @countries t2 on c.resident_id = t2.country_id

Might be easier to have a ResidentCountry and Country tables as separate though...

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources