SQL - Two Outer Joins

Question

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.