• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Compare 2 fields In One Table, having issues

I am using the following query:

select * FROM dbo.sur_response_answer
Where item_id='2560'

This Reports Back 851 Rows

I then use the following query:

select * FROM dbo.sur_response_answer
Where item_id='2561'

(changed 2560 to 2561)
This reports back 854 Rows

How do I compare the 2 query and display the 3 rows that are not in the first query?

0
corcent
Asked:
corcent
  • 7
  • 6
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Well, Item_ID difference would make all 854 rows different than the 851 you got previously; however, say you are using SQL Server and you are selecting columns that would make these records similar regardless of the Item_ID, you could do this:

SELECT col1, col2, col3
FROM dbo.sur_response_answer
WHERE item_id = '2561'
EXCEPT
SELECT col1, col2, col3
FROM dbo.sur_response_answer
WHERE item_id = '2560'

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Or alternatively, you can do like this (given there is a column you use to determine existence):

SELECT a.*
FROM dbo.sur_response_answer a
WHERE a.item_id = '2561'
AND NOT EXISTS (SELECT 1 FROM dbo.sur_response_answer b
WHERE b.item_id = '2560' AND b.Col1 = a.Col1 AND b.Col2 = a.Col2)
0
 
corcentAuthor Commented:
Neither of these worked for me, to give you a little more background the ROWS are answers from a survey so for instance.

Question 1 (2560) = 851 answers
Question 2 (2560) = 854 answers

Obviously odd and I want to determine the extra three answers in question 2.

The table is: dbo.sur_response_answer

The columns are: response_id, item_id, answer_id

The response_id and item_id are the same in both questions but the questions numbers (2560, 2561) are different.

0
 
corcentAuthor Commented:
Let me restate the above:

Neither of these worked for me, to give you a little more background the ROWS are answers from a survey so for instance.

Question 1 (2560) = 851 answers
Question 2 (2561) = 854 answers

Obviously odd and I want to determine the extra three answers in question 2.

The table is: dbo.sur_response_answer

The columns are: response_id, item_id, answer_id

The response_id and item_id are the same in both questions but the questions numbers (2560, 2561) are different.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Kevin CrossChief Technology OfficerCommented:
SELECT a.*
FROM dbo.sur_response_answer a
WHERE a.item_id = '2561'
AND NOT EXISTS (SELECT 1 FROM dbo.sur_response_answer b
WHERE b.item_id = '2560' AND b.answer_id = a.answer_id)

This should give you the records that are not in the previous query.  I used the answer_id since you indicated that the response_id is the same in both queries. Please provide sample of data returned from each query if this doesn't help.
0
 
corcentAuthor Commented:
This:

SELECT a.*
FROM dbo.sur_response_answer a
WHERE a.item_id = '2561'
AND NOT EXISTS (SELECT 1 FROM dbo.sur_response_answer b
WHERE b.item_id = '2560' AND b.answer_id = a.answer_id)

is just displaying the records of the top Item_ID, for instance the above query shows 854 Rows of data and if I reverse the ITEM_ID's it shows 851 rows of data, not the 3 rows that are different?

Thank you for your help.
0
 
Kevin CrossChief Technology OfficerCommented:
Guess I am not making it clear that you have to find the column of data that would be common over the queries and check for that.  I am in the dark until you provide sample data.

This result is what I would expect to see if answer_id is the pk for example.  I would think you would want response_id, but you said this value was the same so figured that wouldn't tell you anything; however, you can replace answer_id above with response_id to see if that makes it work.
0
 
corcentAuthor Commented:
This:
select * FROM dbo.sur_response_answer
Where item_id='2560'

Produces the following data (the entire data is 851 rows, this is just 5 for example)

response_id   item_id      answer_id
2282            2560            11934      
2286            2560            11936      
2292            2560            11938      
2296            2560            12366      
2297            2560            12366      

Then this:

select * FROM dbo.sur_response_answer
Where item_id='2561'

produces:

response_id   item_id      answer_id
2282             2561            11944
2286             2561            11945
2292             2561            11944
2296             2561            11944
2297             2561            11944


Let me know if that helps.
0
 
Kevin CrossChief Technology OfficerCommented:
SELECT a.*
FROM dbo.sur_response_answer a
WHERE a.item_id = '2561'
AND NOT EXISTS (SELECT 1 FROM dbo.sur_response_answer b
WHERE b.item_id = '2560' AND b.response_id = a.response_id)

-- OR --

SELECT a.*
FROM dbo.sur_response_answer a
WHERE a.item_id = '2561'
AND a.response_id NOT IN (SELECT response_id FROM dbo.sur_response_answer b
WHERE b.item_id = '2560')
0
 
corcentAuthor Commented:
Both of these produced 0 rows, again I appreciate the assistance, if there is anything else you would need let me know.
0
 
Kevin CrossChief Technology OfficerCommented:
Do this:

SELECT DISTINCT response_id
FROM dbo.sur_response_answer
WHERE item_id = '2561'

SELECT DISTINCT response_id
FROM dbo.sur_response_answer
WHERE item_id = '2560'

What is the count of records on each?
0
 
Kevin CrossChief Technology OfficerCommented:
If these are the same, use this to find the duplicate rows:

SELECT response_id
FROM dbo.sur_response_answer
WHERE item_id = '2561'
GROUP BY response_id
HAVING (COUNT(answer_id) > 1)
0
 
corcentAuthor Commented:
The numbers were the same and the last query helped me find the duplicates, thank you!
0
 
Kevin CrossChief Technology OfficerCommented:
No problem.  Glad that helped!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now