"Merge" two SQL Server 2000 queries, like Oracle INTERSECT

I have two SQL Server queries I'm manipulating in ColdFusion that I really need to be able to manipulate as one query.

If I were running Oracle, INTERSECT in the query would do what I want in a snap, but no such luck.

Any graceful way to fake an INTERSECT in SQL Server? I expect large datasets, so it would need to be pretty efficient. The two queries are as follows. You can see their results columns are exactly the same.

<cfquery name="GetAllMultipleChoiceResponses" datasource="quiz1">
     SELECT RespondentID, QuestionID, OptionValue AS Response
     FROM MultipleChoiceResponses, Respondents, MultipleChoiceOptions
     WHERE MultipleChoiceResponses.RespondentID = Respondents.ID
     AND MultipleChoiceResponses.OptionID = MultipleChoiceOptions.ID
     AND Respondents.SurveyID = #GetSurveyID#
</cfquery>

<cfquery name="GetAllTextResponses" datasource="quiz1">
     SELECT RespondentID, QuestionID, AnswerText AS Response
     FROM TextResponses, Respondents
     WHERE TextResponses.RespondentID = Respondents.ID
     AND Respondents.SurveyID = #GetSurveyID#
</cfquery>

Bonus points (I can do that, right?) for being able to sort the resulting/combined query by RespondentID ASC, QuestionID ASC!
LVL 1
SapphireblueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
Don't know Oracle, but it looks like you are asking for UNION  (or UNION ALL)

Select A,B From table1

Union

Select A,B From table 2

Order bY (your fields here)

Am I understanding the question?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SapphireblueAuthor Commented:
Yeah. Someone else pointed out on another section that that's what I wanted, and when he said it, I realized INTERSECT is a whole different thing entirely (gets results that are common to BOTH queries).

So yeah, I'm feeling stupid, but thanks a lot for your help :D
0
Anthony PerkinsCommented:
0
Kevin HillSr. SQL Server DBACommented:
Good link Anthony...thanks
0
SapphireblueAuthor Commented:
Sorry acperkins. I am clearly a dork.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.