数据库连接查询 sqlserver 2000

-- 创建 一个表包含下面三个表的id字段，并创建下面三个表，每个表有自己的名称字段

CREATE TABLE jobs

(

   job_id  smallint

      IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   cityID       int,

   provinceID int,

   countryID int

)

 

CREATE TABLE city

(

   cityID  smallint

      IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   cityName       int

 

)

 

CREATE TABLE province

(

   provinceID  smallint

      IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   provinceName       int

)

 

CREATE TABLE country

(

   countryID  smallint

      IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   countryName       int

)

 

-----查询jobs表，显示jobs表的所有数据并把三个外键查询为三个外键表的名称值

select userName, cityName,  productName,countryName, provinceName from

(

select userName, cityName, productName, countryName, provinceID,countryID from

( 

( select userName, cityName,  productName, countryID,provinceID  from jobs as t1 LEFT JOIN  city as t2 on t1.cityID = t2.ID ) as a 

   left join country t3 on a.countryID = t3.ID  

) 

) as b 

left join province t4 on b.provinceID = t4.ID