assigning a label or column to a union query

[sap1958]

declare @include1 table
(
attrid char(10),
attrtype char(6)
)
insert into @include1(attrid,attrtype)
select attrid,attrtype
from attribute
where attrype in ('s1','s2')

declare @include2 table
(
schoolID char(10),
school char(6)
)
insert into@include2(schoolID,school)
select schoolID,school
from schoolTable
where school = '2'

select coreid
from coreTable
inner join @include1
on coreid = attrid

union
select coreid
from coreTable
inner join @include2
on coreid=schoolID

Now I want to identify who was selected in which group. For example if a person was selected in @include1 then I want a column that says 'include1', same for @include2. Is this done through a row() function or something else

 

[gmmastros]

I think the best way to handle this is to just hard code your data, like this...

select coreid[!],'Include1' As IncludeGroup[/!]
from coreTable
inner join @include1
on coreid = attrid

union
select coreid[!],'Include2'[/!]
from coreTable
inner join @include2
on coreid=schoolID

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Actually.... there is a potential problem with my suggestion that you should be made aware of...

A union query will only return distinct rows. By hard coding the data like I suggested, it's possible for you to now get more rows than you had before. Here's the situation...

Suppose @Include1 has data like this:

[tt]
attrid,attrtype
1 a
2 b
[/tt]

Suppose @Include2 has data like this:
[tt]
SchoolId,School
1 a
3 c
[/tt]

A union all query would return
1,a
2,b
1,a
3,c

A union query would return
1,a
2,b
3,c

By hard coding the data, you are forcing the data from both tables to be different, so a union all with the hard coded data would return this:

1,a,Include1
2,b,Include1
1,a,Include2
3,c,Include2

A UNION query would return the same data as the union all because the hard coded data for id = 1 is different.

Depending on your data, this may or may not be the right thing to do. You'll have to decide, but at least you have more information to base your decision on.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom