Two joins between two tables

Question

I have two tables to store addresses as follows:

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?

Answer 1

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] 

Answer 2

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] 

Answer 3

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)

Answer 4

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