CODEWITHSUNDEEP
sqlsql-server-2008
rahularyansharma
rahularyansharma

Reputation: 10765

inner join to table depend on values in first table column join second or third table

I have four tables User,Destination,City and visited

as follows 

declare  @user table
 (
   UserId int identity(1,1) not null ,
   UserName nvarchar(500)
 )

 insert into @user (UserName) values ('rahul')
 insert into @user (UserName) values ('nitin')
 insert into @user (UserName) values ('yunus')

 declare @destination table 
  (
    destCode nvarchar(50),
    destName nvarchar(500)
  )

  insert into @destination select 'SWDP','ranthambore national park '
  insert into @destination select 'BTP','ghana national park '

  declare @city table 
   (
     cityId int identity(1,1) not null, 
     cityname nvarchar(500)
   )

 insert into @city select 'jaipur'
 insert into @city select 'delhi'

 -- visited table in which user id with either destCode or cityId

 declare @visited table 
   (
     UserId int , 
     LocationFrom nvarchar(500),
     LocationTo nvarchar(500),
     LocType nvarchar(50)
   )

  insert into @visited select '1','BTP','1','city'
  insert into @visited select '1','1','SWDP','dest'


  insert into @visited select '2','1','2','city'


  insert into @visited select '3','2','SWDP','dest'
  insert into @visited select '3','SWDP','BTP','dest'


 select * from @user 

 select * from @destination

 select * from @city

 select * from @visited

  select * from @visited as v
   inner join @user as u ON v.UserId=u.UserId

Location type column in visited table denotes the LocationTo type. Its either city or location .

Now i want to join these tables so i can get the data which user started from which city or destination and go to which city and destination .

Expected result is as below image

this is only for one user

This is only for one user but i need same for all users .

Upvotes: 0

Views: 918

Answers (2)

Tobsey
Tobsey

Reputation: 3400

This should work for you:

;WITH Locations AS
   (SELECT
        CAST(CityID as nvarchar(50)) LocationId,
        CityName LocationName
    FROM
        @city
    UNION ALL
    SELECT
        DestCode,
        DestName
    FROM
        @destination)
    SELECT 
        Users.UserName,
        LocationsFrom.LocationName LocationFromName, 
        LocationsTo.LocationName LocationToName
    FROM 
        @visited Visited
        INNER JOIN Locations LocationsFrom ON LocationsFrom.LocationID = Visited.LocationFrom
        INNER JOIN Locations LocationsTo ON LocationsTo.LocationID = Visited.LocationTo
        INNER JOIN @user Users ON Visited.UserID = Users.UserID

Upvotes: 2

Urban Björkman
Urban Björkman

Reputation: 2105

I am not sure if i understand your table structure correctly but as i see it the table @destination can be both an origin and an endpoint? and the LocType only specifies the type of the endpoint (LocationTo in @visited) Given this i thik you have to assume that a numeric value in @Visited.LocationFrom specifies an Id in @city

then this query might be of use

  SELECT u.UserName,   
    CASE WHEN ISNUMERIC(v.LocationFrom) = 1 THEN (SELECT cityName FROM @city where cityId = cast(v.LocationFrom as int)) ELSE (SELECT destName FROM @destination where destCode = v.LocationFrom) END as LocationFrom,
    CASE WHEN ISNUMERIC(v.LocationTo) = 1 THEN (SELECT cityName FROM @city where cityId = cast(v.LocationTo as int)) ELSE (SELECT destName FROM @destination where destCode = v.LocationTo) END as LocationTo
    FROM @visited AS v
   inner join @user AS u ON v.UserId=u.UserId

Upvotes: 2

Related Questions