CODEWITHSUNDEEP
sqlsql-server
Alaa Jabre
Alaa Jabre

Reputation: 1883

Two joins between two tables

I have two tables to store addresses as follows:enter image description here

I want to get every address, city, name and area name (cityId,Areaid maybe null)

I tried:

SELECT [Details]
      ,[AddressId]
      ,[CityId]
      ,[PlaceName] as CityName
      ,[AreaId]
      ,[PlaceName] as AreaName
  FROM [MyDB].[dbo].[Address] LEFT OUTER JOIN [MyDB].[dbo].[Places]
  ON [CityId] = [PlaceI]

but that will give me only the names of the city. How to get the area name, too?

Upvotes: 1

Views: 109

Answers (4)

Miller
Miller

Reputation: 1156

Try This

SELECT [Details]
      ,[AddressId]
      ,[CityId]
      ,C.[PlaceName] as CityName
      ,[AreaId]
      ,A.[PlaceName] as AreaName
FROM [MyDB].[dbo].[Address] 
     INNER JOIN [MyDB].[dbo].[Places] C
     ON ([CityId] = [PlaceID] AND IsCity=1)
     OR  ([AreaId] = [PlaceID] AND IsCity=0)

Upvotes: 0

juergen d
juergen d

Reputation: 204746

Add another join

SELECT [Details]
      ,[AddressId]
      ,[CityId]
      ,p1.[PlaceName] as CityName
      ,[AreaId]
      ,p2.[PlaceName] as AreaName
  FROM [MyDB].[dbo].[Address] 
  LEFT OUTER JOIN [MyDB].[dbo].[Places] p1 ON [CityId] = p1.[PlaceId]  
  LEFT OUTER JOIN [MyDB].[dbo].[Places] p2 ON [AreaId] = p2.[PlaceId]

Upvotes: 3

user1023993
user1023993

Reputation: 151

will adding another join to the same table ... this time joining for the area id work? (other answers dropped in so not adding the query)

you may want to add another column to places to join for the area id or drop in a third a table to get rid of the many to many relationships if possible

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

You need to join twice with the Places table. Once for the cities and once for the Areas.

SELECT [Details]
      ,[AddressId]
      ,[CityId]
      ,C.[PlaceName] as CityName
      ,[AreaId]
      ,A.[PlaceName] as AreaName
FROM [MyDB].[dbo].[Address] 
     LEFT OUTER JOIN [MyDB].[dbo].[Places] C
         ON [CityId] = C.[PlaceI] 
     LEFT OUTER JOIN [MyDB].[dbo].[Places] A
         ON [AreaId] = A.[PlaceI]

Upvotes: 3

Related Questions