Solved

join two resultsets in sql server

Posted on 2010-11-18
7
654 Views
Last Modified: 2012-05-10
I have two resultsets I want to join.  But im not sure the easiest and best way.

Here are both queries:


SELECT     TOP (1) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
FROM         vw_Events
WHERE     (Highlight = 'Yes' and Priority = 'Yes')
ORDER BY StartDate

SELECT     TOP (2) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
FROM         vw_Events
WHERE     (Highlight = 'Yes' and Priority = 'NO')
ORDER BY StartDate
0
Comment
Question by:rochestermn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34167826
Try UNION:

SELECT     TOP (1) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
FROM         vw_Events
WHERE     (Highlight = 'Yes' and Priority = 'Yes')
ORDER BY StartDate
UNION
SELECT     TOP (2) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
FROM         vw_Events
WHERE     (Highlight = 'Yes' and Priority = 'NO')
ORDER BY StartDate 

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 34167837
what do you mean by "join". Do you mean merge? then use UNION ALL like below
SELECT     TOP (1) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
FROM         vw_Events
WHERE     (Highlight = 'Yes' and Priority = 'Yes')
union all
SELECT     TOP (2) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
FROM         vw_Events
WHERE     (Highlight = 'Yes' and Priority = 'NO')
ORDER BY StartDate

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 34167895
Actually try
select * from (
	SELECT     TOP 1 EventID,EventDescription,StartDate,EndDate,Highlight, Priority
	FROM         vw_Events
	WHERE     (Highlight = 'Yes' and Priority = 'Yes')
	order by StartDate
) a
union all
select * from (
	SELECT     TOP 2 EventID,EventDescription,StartDate,EndDate,Highlight, Priority
	FROM         vw_Events
	WHERE     (Highlight = 'Yes' and Priority = 'NO')
	ORDER BY StartDate
) b

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34167929
Just in case you are wondering what the difference is between UNION and UNION ALL,

UNION ALL gives you everything including duplicates while UNION gives you an occurance of each record.

So, you choose based on your need but the solutions are there, I think.
0
 
LVL 2

Expert Comment

by:vasureddym
ID: 34188596
rochestermn, you can also try CTE to define (name) the two result set and do the union. something like below.



sammySeltzer: the difference between your code and ralmada's is not just the UNION vs UNION ALL. the ORDER BY clause cannot be directly used so. just an FYI.

with top1 as (
	SELECT     TOP (1) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
	FROM         vw_Events
	WHERE     (Highlight = 'Yes' and Priority = 'Yes')
	ORDER BY StartDate
)
,top2 as (
	SELECT     TOP (2) EventID,EventDescription,StartDate,EndDate,Highlight, Priority
	FROM         vw_Events
	WHERE     (Highlight = 'Yes' and Priority = 'NO')
	ORDER BY StartDate
) 
select * from top1
union all
select * from top2;

Open in new window

0
 

Accepted Solution

by:
rochestermn earned 0 total points
ID: 34239610
I decided to go with a temp table.  It worked just fine.  Thanks for all the union suggestions though Im sure I will use them in the future.
0
 

Author Closing Comment

by:rochestermn
ID: 34276715
I dediced to use my own solution...a temporary table vs using union.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question