Multiple Query Rows in Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple Query Rows in Union

Author

Topic

slserra
Starting Member

19 Posts

Posted - 2002-12-27 : 12:43:11

I need some help wih the following query that returns all announcements and should indicate whether a particular member is designated as a recipient or not (I pared down the code for readability):

-- Get announcements for all Groups the member belongs to
SELECT DISTINCT
a.PKId,
a.CommunityId,
a.Title,
'Y' as Recipient,
a.Description
FROM tbl_Announcement a, tbl_GroupAnnouncement ga, tbl_GroupMember gm
WITH (NOLOCK)
WHERE a.PKId = ga.AnnouncementId
AND ga.GroupId = gm.GroupId
AND gm.MemberId = @MemberId

UNION

-- Get announcements where the member is not a member of the group
SELECT DISTINCT
a.PKId,
a.CommunityId,
a.Title,
'N' as Recipient,
a.Description
FROM tbl_Announcement a, tbl_GroupAnnouncement ga
WITH (NOLOCK)
WHERE a.PKId = ga.AnnouncementId
AND ga.GroupId not in (select GroupId from tbl_GroupMember
where MemberId = @MemberId)

Announcements can be assigned to multiple groups as contained in the GroupAnnouncement table. A person can belong to multiple groups as contained in the GroupMember table.

The query should return all announcements and indicate whether the member is a recipient of the particular announcement. The query should return one row per announcement and have a Recipient = 'Y' if the announcement is assigned to a group that the member belongs to. The announcement should only have a Recipient = 'N' if the announcement is assigned to groups that the member is not a member of (i.e. the top select does not return any rows for the particular announcement).

What's happening is that two rows are being returned if the announcement is assigned to multiple groups at least one of which the member belongs to and one of which the member does not belong to.

Any thoughts on how to have the query only return a single row per announcement?

Thanks,

Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-27 : 14:40:37

You don't want to UNION the two queries, you want to do a LEFT OUTER JOIN from one to the other.

This gives you a list of all announcements that apply to the current member; the key is to return only a list of PKID's and only 1 per announcement:

SELECT A.PKID FROM
tbl_GroupAnnouncement A
INNER JOIN tbl_GroupAnnouncement GA
ON GA.announcementID = A.PKID
INNER JOIN tbl_GroupMember GM
ON GM.GroupID = GA.GroupID
WHERE gm.MemberID = @MemberID
GROUP BY A.PKID

Thus, we take the table of all announcements, do a LEFT OUTER JOIN to the above, and if we get a match, then it applies to the @memberID; if not, it doesn't. We don't filter this result so that all announcements are shown, along with a 'N' or a 'Y':

SELECT T.*, CASE WHEN A.PKID IS NULL THEN 'N' ELSE 'Y' END as Recipient
FROM
tbl_announcement T
LEFT OUTER JOIN
(above SQL) A
ON
T.PKID = A.PKID

- Jeff

slserra
Starting Member

19 Posts

Posted - 2002-12-27 : 18:15:05

Thanks. I think I see what your doing.... Here's another question on this topic.

For brevity the original post did not include many details surrounding announcements. Announcements can be of 3 types (I,G,P). Individual announcements (type 'I') are personal reminders and are visible only to the creator. Public announcements (type 'P') are visible to everyone. Group announcements (type 'G') are only visible to A.) members of the group or B.) everyone, if the group allows non-members to view their postings. In additon, there are two types of groups--Predefined and ad-hoc. Predefined groups are defined in the GroupMember table and represent things such as the Finance dept or Accounting. Ad hoc groups are simply a collection of inidividual member ids. Both of these group types are stored in the GroupAnnouncement table--a single entry per predefined group and a single entry for each individual member id.

The basic structure of my query is the following

Get personal announcements (type 'I')
UNION
Get Group announcements for a specific group (type 'G', predetermined group)
UNION
Get Group announcements for all groups the member belongs to (type 'G', predetermined group)
UNION
Get Group announcements for groups the member does not belong to and the group allows non-members access (type 'G', predetermined group) - Recipient = 'N'.
UNION
Get Group announcements for individuals (type 'G', ad-hoc group)
UNION
Get Public announcements

All of the above queries have a Recipient = 'Y' except for the one indicated.

I do not see how to mesh your suggested approach into my heavily UNIONed query. Thoughts?

Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-30 : 13:13:06

Steve --

The key is the CASE statement I gave you. just alter it to accomidate other accouncement types.

For example,

CASE WHEN A.PKID Is not Null THEN 'Y' ELSE
WHEN Type ='I' and creator = @MemberID THEN 'Y' ELSE
WHEN Type in ('P','G') THEN 'Y' ELSE
'N' END

or something like that. Again, you shouldn't need UNIONS unless you are working with multiple tables that the announcements live in.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources