Compare Two Rows of a Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare Two Rows of a Table

Author

Topic

prakashdotc
Starting Member

25 Posts

Posted - 2009-05-28 : 08:06:10

Hi All,

I have "Answers" table with following columns "CustomerID, QuestionID, Answers"

CustomerID foreign key to Customer Table which holds customer details

QuestionID foreign key to Question Table which holds number of Questions

Customer is asked to provide answers to 2 Question which is randomly selected from Question Table

How to Compare the 2 Answers provided by the customer with respect to CustomerID and QuestionID in the "Answers" Table

If I'm not clear with the Problem please comment, i'll try to present in a better way.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 08:09:31

You have to explain better was "compare" means to you.

E 12°55'05.63"
N 56°04'39.26"

prakashdotc
Starting Member

25 Posts

Posted - 2009-05-28 : 08:16:50

Hi Peso,

The Answers Table will have data as

--------------------------------------------------------------
CustomerID - QuestionID - Answers
--------------------------------------------------------------
12 - 1 - Jack
12 - 2 - America
12 - 3 - 8
12 - 4 - Jan Street
12 - 5 - Drums
12 - 6 - Football
13 - 1 - Matt
13 - 2 - China
...
--------------------------------------------------------------

Customer is asked to provide answers to any of the 2 questions selected randomly,
those answers has to be compared with the data in this table with respect to
CustomerID, QuestionID and Answers

We have CustomerID, Randomly Selected 2 QuestionID and Answers to those Questions

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 08:20:56

so what is the expected result like ?

KH
[spoiler]Time is always against us[/spoiler]

prakashdotc
Starting Member

25 Posts

Posted - 2009-05-28 : 08:25:33

Hi khtan,

Say that the Randomly Selected Question for the Customer is 2 and 4,

So Customer will provide answers to these two questions.

the Stored Procedure will have Following Input Parameters

CustomerID
Question1ID
Answer1
Question2ID
Answer2

Based on this we have to validate the answers with respect to question id's for the customer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 08:34:26

What do you want to validate?

1. That Question1ID and Question2ID exists in the database?
2. That the two ID hasn't been used before for that user?
3. See if any other user has made the same answer?

E 12°55'05.63"
N 56°04'39.26"

prakashdotc
Starting Member

25 Posts

Posted - 2009-05-28 : 08:59:20

Hi Peso,

The Where Condition will be as

Select CustomerID from Answers WHERE Question1ID = Answer1 AND Question2ID = Answer2 AND CustomerID = 12

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:11:58

SELECT CustomerID
FROM Answers
GROUP BY CustomerID
HAVING MAX(CASE WHEN QuestionID = @Question1ID THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN QuestionID = @Question2ID THEN 1 ELSE 0 END) = 1

To get all customers which has answered both @Question1ID and @Question2ID

E 12°55'05.63"
N 56°04'39.26"

prakashdotc
Starting Member

25 Posts

Posted - 2009-05-28 : 09:32:58

It's not just getting customer who has answered both @Question1ID and @Question2ID

The @Question1ID may the anything between 1 to 10 and same for @Question2ID also

Two Questions is Selected Randomly from the list of 10 Questions.

Customer will provide appropriate answers to those questions.

Those answers provided by the customer has to be compared with the Answers Table for that customer with appropriate questionID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:44:40

[code]CREATE PROCEDURE dbo.uspMyAnswerProcedure
(
@CustomerID INT,
@Question1ID INT,
@Answer1 VARCHAR(MAX),
@Question2ID INT,
@Answer2 VARCHAR(MAX)
)
AS

SET NOCOUNT ON

UPDATE Answers
SET Answer = @Answer1
WHERE QuestionID = @Question1ID

IF @@ROWCOUNT = 0
INSERT Answers
SELECT CustomerID,
@Question1ID,
@Answer1

UPDATE Answers
SET Answer = @Answer2
WHERE QuestionID = @Question2ID

IF @@ROWCOUNT = 0
INSERT Answers
SELECT CustomerID,
@Question2ID,
@Answer2
END[/code]

E 12°55'05.63"
N 56°04'39.26"

prakashdotc
Starting Member

25 Posts

Posted - 2009-05-28 : 10:43:00

Hi Peso,

This is not to insert into the Table, Just to Validate the weather Customers Answer to the Respective Question ID is matching (correct) or not.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 11:01:16

[code]CREATE FUNCTION dbo.fnMyAnswerCheck
(
@CustomerID INT,
@Question1ID INT,
@Answer1 VARCHAR(MAX),
@Question2ID INT,
@Answer2 VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
DECLARE @A1 BIT,
@A2 BIT

SELECT @A1 = 0,
@A2 = 0

IF EXISTS (SELECT * FROM Answers WHERE CustomerID = @CustomerID AND QuestionID = @Question1ID AND Answer = @Answer1)
SET @A1 = 1

IF EXISTS (SELECT * FROM Answers WHERE CustomerID = @CustomerID AND QuestionID = @Question2ID AND Answer = @Answer2)
SET @A2 = 1

RETURN @A1 & @A2
END[/code]

E 12°55'05.63"
N 56°04'39.26"

prakashdotc
Starting Member

25 Posts

Posted - 2009-06-10 : 00:13:37

Hi Peso,

Thank for u'r Help and sorry for late reply.

And here is by SP, which i framed.


CREATE PROCEDURE ValidateAnswers
     @CustomerID	INT,
     @Question1ID	INT,
     @Answer1		NVARCHAR(100),
     @Question2ID	INT,
     @Answer2		NVARCHAR(100),
     @Valid		INT OUTPUT
AS
BEGIN
     IF EXISTS(SELECT CustomerID FROM ANSWERS WHERE QuestionID = @Question1ID AND Answers = @Answer1 AND CustomerID = @CustomerID)
     BEGIN
          IF EXISTS(SELECT CustomerID FROM ANSWERS WHERE QuestionID = @Question2ID AND Answers = @Answer2 AND CustomerID =   
                             @CustomerID)
          BEGIN
               SET @Valid = 1
          END
          ELSE
          BEGIN
               SET @Valid = 0
          END
     END
     ELSE
     BEGIN
          SET @Valid = 0
      END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources