Getting common rows from two different queries in SQL Server

Question

Is there any way that I can get the common rows in two different queries? Say one query returns 30 rows, and other one returns 100. But there are only 17 rows that have the same IDs... there are two columns, say QuestionID and Answer .. I nbeed to get the same questions answered by two different users. I would appreciate if I can do this on SQL side before starting foreachs in .NET. Thanks in advance

Answer 1

This example uses IN in a correlated (or repeating) subquery, which is a query that depends on the outer query for its values. It is executed repeatedly, once for each row that may be selected by the outer query. This query retrieves one instance of each author’s first and last name for which the royalty percentage in the titleauthor table is 100 and for which the author identification numbers match in the authors and titleauthor tables.

USE pubs

SELECT DISTINCT au_lname, au_fname

FROM authors

WHERE 100 IN

(SELECT royaltyper

FROM titleauthor

WHERE titleauthor.au_id = authors.au_id)

Taken from: http://doc.ddart.net/mssql/sql70/sa-ses_4.htm

Answer 2

INTERSECT may be what you are looking for. See MSDN for details. If you cannot satisfy the requirements for intersection, think about JOINing the two results or using the EXISTS clause. Or create the UNION of both results, GROUP the rows by question id and COUNT the rows per group returning just groups with more than one row.

Answer 3

You should provide us the table structure to help us give you a better answer.
Here's my stab at it:

SELECT *
FROM dbo.Questions q
     INNER JOIN dbo.Answers a ON a.QuestionID = q.QuestionID

Answer 4

I have a table called [Answers] .. its consisted of 3 columns,

UserID QuestionID SelectedIndex

There will be lots of users in the table, and I need to take two-users at a time and see how many common question they have answered, then I will look at the results. But there will be lots of combinations , this is making me depressed :)