SQL - two external joins

Question

I have a table representing a list of countries. I have another table representing a list of states. I have another table representing a list of provinces. Due to poor data definition, some states are actually in the provincial table and vice versa. Despite this, each province and state is associated with a country.

I need to essentially make a double left outer join. My question is: how do I do this? Here is what I'm trying now:

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]

Note that I need to do something comparable to the two left outer joins. This is a simplified version of the query I'm trying to make. Thanks for the help!

Answer 1

, . . NULL . , ?

, , , "--", , . , 3 4 , 12 .

. :

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]

. , .

Answer 2

LEFT OUTER JOIN - , :

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

, , . union. RegionType, 0 1 . , .

- :

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

, (, , )? " " , "", "", "" .. , , , .

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

, , , - .. UNION ALL. , :

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

Answer 6

Finally, I was able to figure it out. You can add as many external joins as you want, the key must put all the qualifiers on the external join line so that you do not lose all zeros. See below, where all qualifiers for agldimension are included on the same line, and not in the where statement.

select distinct
act.account, 
act.[description],
rul.[description],
att1.att_name,
att2.att_name,
att3.att_name,
att4.att_name,
att5.att_name,
att6.att_name,
att7.att_name
from aglaccounts act, aglrules rul  
full outer join agldimension att1 on rul.att_1_id = att1.attribute_id and att1.[status] = 'N' and att1.client = 'PH' 
full outer join agldimension att2 on rul.att_2_id = att2.attribute_id and att2.[status] = 'N' and att2.client = 'PH'
full outer join agldimension att3 on rul.att_3_id = att3.attribute_id and att3.[status] = 'N' and att3.client = 'PH'
full outer join agldimension att4 on rul.att_4_id = att4.attribute_id and att4.[status] = 'N' and att4.client = 'PH'
full outer join agldimension att5 on rul.att_5_id = att5.attribute_id and att5.[status] = 'N' and att5.client = 'PH'
full outer join agldimension att6 on rul.att_6_id = att6.attribute_id and att6.[status] = 'N' and att6.client = 'PH'
full outer join agldimension att7 on rul.att_7_id = att7.attribute_id and att7.[status] = 'N' and att7.client = 'PH'
where 
act.account_rule = rul.account_rule and act.client = 'PH' and act.[status] = 'N'