SQL - Two Outer Joins

Question

Hello,

I have a table that represents a list of countries. I have another table that represents a list of states. I have another table that represents a list of provinces. Because of poor data definition, some states are actually in the province table and vice-versa. Regardless, each province and state is associated with a country.

I need to essentially do a double left outer join. My question is, how do I do this? Here is what I am currently trying:

select
  c.Name as 'CountryName',
  ISNULL(p.[Name], '') as 'ProvinceName',
  ISNULL(s.[Name], '') as 'StateName'
from
  Country c 
    left outer join [Province] p on p.[CountryID]=c.[ID]
    left outer join [State] s on s.[CountryID]=c.[ID]

Please note that I need to do something comparable to two left outer joins. This is a simplified version of the query I'm trying to do. Thank you for your help!

Answer 1

You can do it the way you've stated. There's nothing wrong with that. I wouldn't necessarily replace NULLs with empty strings though. Is there a reason you're doing that?

What you have to be aware of when doing what are essentially two one-to-many joins is that the results are multiplicative. By this I mean that if for a country there are 3 entries in the province table and 4 in the state table you'll get back 12 rows for that country.

It might be more appropriate to do a UNION in these circumstances. For example:

SELECT
  c.Name AS 'CountryName',
  '' AS 'ProvinceName',
  ISNULL(s.[Name], '') AS 'StateName'
FROM Country c 
LEFT OUTER JOIN [Province] p ON p.[CountryID]=c.[ID]
UNION ALL
SELECT
  c.Name AS 'CountryName',
  ISNULL(p.[Name], '') AS 'ProvinceName',
  '' AS 'ProvinceName'
FROM Country c 
LEFT OUTER JOIN [State] s ON s.[CountryID]=c.[ID]

as just one possibility. It really depends on what your data looks like and what you want the end result to be.

Answer 2

A LEFT OUTER JOIN is just a LEFT JOIN, the syntax is simply:

SELECT c.Name AS CountryName
p.Name AS ProvinceName
s.Name AS StateName
FROM Country c
LEFT JOIN Province p ON p.CountryID = c.ID
LEFT JOIN State s ON s.CountryID = c.ID

Answer 3

I'm guessing you want the provinces and states concatenated in one table. For this, you'll want the union operator. I've also added a column RegionType which is 0 for a province and 1 for a state. You might want to apply this pattern as a way to differentiate between the two in your final query.

Try something like:

SELECT c.Name as CountryName, p.Name as RegionName, 0 as RegionType
FROM Country c
LEFT OUTER JOIN Province p on c.ID = p.CountryID
UNION ALL
SELECT c.Name as CountryName, s.Name as RegionName, 1 as RegionType
FROM Country c
LEFT OUTER JOIN State s on s.ID = p.CountryID

Answer 4

while your implementation covers states and provinces, what about territories (e.g. Canada has both, regions and territories) ? might be better off with a single table for "administrative regions" and another table for region types containing "state", "province", "territory", etc. Most of the time you aren't going to care what kind of region it is, just the country id, region id, and region name.

select
c.Name as 'CountryName',
ISNULL(r.[Name], '') as 'RegionName'
from
Country c 
left outer join [Regions] r on r.[CountryID]=c.[ID]

Answer 5

Sounds like your problem is that you have two tables that need to be treated as one - ie, UNION ALL. So how about:

SELECT 
    c.Name as Country
   ,ps.Name as StateOrProvince
FROM        Country c
LEFT JOIN   (SELECT CountryID, Name FROM Province UNION ALL SELECT CountryID, Name FROM State) ps
    ON ps.CountryID = c.ID
;

Rob