Combined Left Outer Join and Full Outer Join

Question

I realize that this may have a simple answer but I can't come up with anything and searching hasn't turned up anything now for a few hours. Basically, the situation is that I have a query with two tables (more than that but for this purpose only two). I'm trying to join the two with a left outer join on one condition and a full outer join on the other. Does anyone have any ideas of how I might accomplish this?

Select ResourceID, Month, Cost, Available
FROM TableA
  JOIN TableB
    ON TableA.ResourceID=TableB.ResourceID --I want only resources in Table A
      AND TableA.Month=TableB.Month --all months in TableA or TableB
                        --but still create a single line for that resource/month

Here's a sample of the data for each table and the expected result

TableA

ResourceID  Month   Cost
1           Jan     30
1           Mar     90
1           Jun     100
1           Aug     120
1           Sep     60
2           Feb     80
2           Apr     90
2           May     60
2           Jul     70
4           Jan     50
4           Feb     60

TableB

ResourceID  Month   Available
1           Jan     120
1           Feb     160
1           Mar     140
1           Apr     150
1           May     130
2           Jan     150
2           Feb     100
2           Mar     150
2           Apr     120
2           May     125
3           Jan     170
3           Feb     140
3           Mar     180

Result

ResourceID  Month   Cost    Available
1           Jan     30      120
1           Feb             160
1           Mar     90      140
1           Apr             150
1           May             130
1           Jun     100 
1           Jul             120
1           Aug     120 
1           Sep     60  
2           Jan             150
2           Feb     80      100
2           Mar             150
2           Apr     90      120
2           May     60      125
2           Jul     70  
4           Jan     50  
4           Feb     60  

Any help is greatly appreciated!

Answer 1

You could build your result as an outer join and then filter out the records that come from B without a corresponding A entry

Select Coalesce(TableA.ResourceID, TableB.ResourceID) as ResourceID
     , Coalesce(TableA.Month,TableB.Month) as Month, Cost, Available
FROM TableA OUTER JOIN TableB
    ON TableA.ResourceID=TableB.ResourceID --I want only resources in Table A
        AND TableA.Month=TableB.Month --all months in TableA or TableB
        --but still create a single line for that resource/month
WHERE TableB.ResourceID IS NULL OR TableB.ResourceID IN (SELECT DISTINCT ResourceID FROM TableA)

Because if it came from Table A without a match in Table B, then TableB.ResourceID will be NULL and you'll keep it.
If it came from both (matched both RedID and Month) then TableB.ResourceID will be in A and the second half of the WHERE clause will let it stay.
If it came from B but has a matching ResID in A then the second half of the WHERE clause will also keep it, even though it doesn't have a corresponding (ID,Month) pair in A.

You need the COALESCE because if the second half of the WHERE clause retains a record that was from B without an matching ID,Month in A, then the A.ID and A.Month will be NULL.

Answer 2

If you want resources in table A and months from both tables, then I would approach this more using a union:

select ResourceId, Month, sum(Cost) as cost, sum(Available) as available
from ((select ResourceId, Month, Cost, NULL as Available
       from tableA
      ) union all
      (select ResourceId, Month, NULL as Cost, Available
       from tableB
       where exists (select 1 from tableA where tableA.resourceId = tableB.resourceId)
      )
     ) ab
group by ResourceId, Month
order by ResourceId, Month;