Which type of join can I use to reproduce these results.

Question

I have the following view which contains this data

ActivityRecId RegionRecId IsExcluded
1             null        1
2             null        1
3             1           1
3             2           1
4             1           1
5             null        0

What I would like to do is join the region table to the view above to get the following records.

ActivityRecId RegionRecId IsExcluded
1             null        1
2             null        1
3             1           1
3             2           1
3             3           0
3             4           0
4             1           1
4             2           0
4             3           0
4             4           0
5             null        0

The region table has the following columns:

• RegionRecId
• RegionName

Any suggestions. Let me know if you need any other information.

--------------------- CORRECT QUESTION ------------------------
ActivityRecId RegionRecId IsExcluded
    1             null        1
    2             null        1
    3             1           1
    3             2           1
    3             3           0
    3             4           0
    4             1           1
    4             2           0
    4             3           0
    4             4           0
    5             1           0
    5             2           0
    5             3           0
    5             4           0

If it makes it easier activity 1 and 2 can list all the regions also.

Thanks,

Answer 1

Here's great reference to take a look at for joins. I think you'd need a Left Outer Join

Answer 2

I don't have an SQL Server handy to test this, but would something like

select  *
from    myView

union

select  myView.ActivityRecId,
        region.RegionRecId,
        0 as IsExcluded
from    myView cross join region
where   (myView.RegionRecId is not null or myView.IsExcluded = 0)
        and not exists (
            select  null
            from    myView
            where   myView.RegionRecId = region.RegionRecId
        )

be what you want?