Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2005-03-02
Medium Priority
Last Modified: 2007-11-27
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 name="GetAllTextResponses" datasource="quiz1">
     SELECT RespondentID, QuestionID, AnswerText AS Response
     FROM TextResponses, Respondents
     WHERE TextResponses.RespondentID = Respondents.ID
     AND Respondents.SurveyID = #GetSurveyID#

Bonus points (I can do that, right?) for being able to sort the resulting/combined query by RespondentID ASC, QuestionID ASC!
Question by:Sapphireblue
  • 2
  • 2
LVL 21

Accepted Solution

Kevin3NF earned 2000 total points
ID: 13443682
Don't know Oracle, but it looks like you are asking for UNION  (or UNION ALL)

Select A,B From table1


Select A,B From table 2

Order bY (your fields here)

Am I understanding the question?

Author Comment

ID: 13443753
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
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13443761
LVL 21

Expert Comment

ID: 13443842
Good link Anthony...thanks

Author Comment

ID: 13443912
Sorry acperkins. I am clearly a dork.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

578 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