asked on

join two resultsets in sql server

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

sammySeltzer

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

ralmada

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

ralmada

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

sammySeltzer

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.

vasureddym

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

ASKER CERTIFIED SOLUTION

rochestermn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rochestermn

ASKER

I dediced to use my own solution...a temporary table vs using union.