comparing inputs - 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

Database Design and Application Architecture

comparing inputs

Author

Topic

socox
Starting Member

13 Posts

Posted - 2009-03-15 : 11:40:49

How can I compare the entries from two tables? And if they are the same,then fore example, return 1 if not 0...if SQL have that options?

I'am new in this

THX

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 12:06:05

Read about the tablediff utility. This will let you compare 2 entire tables. If you're comparing just one record you would try a select statement and inner join on the fields you want to compare. No result means they're unequal.

Mike
"oh, that monkey is going to pay"

socox
Starting Member

13 Posts

Posted - 2009-03-15 : 14:43:49

ok...I need to compare two columns, answers with correct answers from another table...when I compare them I need to sum points into the third table...so how can I,when I compare results, save that 0 or 1,that answer they are equal or unequal, into that third table...or is there some other way, I really need help with this??

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 15:22:17

Without knowing your table structure I created an example. I'm guessing you'd have some sort of column to indicate the question which would exist in both tables. In seta a you have an answer. Let's say these are all the correct answers. In setb we have the answers that a respondent gave. The query below evaluates each answer in both tables and if it's right sets a column called "CORRECT" to 1 if not it sets "Correct" to 0. This can become the update portion of your code to update your 3rd table.

create table #seta(question int, answer char(1))
create table #setb(question int, answer char(1))

insert into #seta(question,answer) values(1,'Y'),(2,'N'),(3,'N')
insert into #setb(question,answer) values(1,'Y'),(2,'Y'),(3,'N')

select a.question,a.answer,b.answer,case when a.answer=b.answer then 1 else 0 end as Correct
from #seta a
inner join #setb b
on a.question=b.question

Mike
"oh, that monkey is going to pay"

socox
Starting Member

13 Posts

Posted - 2009-03-15 : 17:26:38

great, that's table structure how I imagine it,table with questions and correct answers and table with wrong answers.So when I'am creating stored procedure,for application, it goes like this?

CREATE PROCEDURE spCorrect
AS
SELECT a.question,a.answer,b.answer,case when a.answer=b.answer then 1 else 0 end as Correct
FROM #seta a
INNER JOIN #setb b
ON a.question=b.question

Is it better to create temporary procedures or "regular" ones? For this case I dont need temporary,but I'am asking for those which lead to this one.
thank you for your help...

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 18:37:44

A regular stored procedure makes sense. I rarely use temporary SP's. I just created some temp tables here by way of example because it doesn't leave a lot of clutter on my machine.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources