how to count matches between two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to count matches between two tables?

Author

Topic

rog2054
Starting Member

5 Posts

Posted - 2007-11-24 : 10:26:03

Hello. I'm quite new to SQL so have included as many details as i can think of here. The scenario is a wordsearch style puzzle. The user can select their answers in any order and these answers are stored in a table. I need help with the UPDATE statement to compare the users answers against the correct answers for that puzzle.
(Note: In the actual scenario there will be 10-15 answers per grid, but i have reduced the number to make testing easier - hopefully the code for a working UPDATE statement will be scalable to account for grids with different numbers of answers etc.)

The Tables:
-- These are the correct answers for a given grid (gridid).
-- Due to the nature of the puzzle, answers can be in any order.
-- Each level may contain one,two,or no 'bonusanswers' which are harder to find, so these are scored separately so bonus points can be awarded.
CREATE TABLE correctanswers
(
gridid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
answer1 char(15),
answer2 char(15),
bonusanswer1 char(15),
bonusanswer2 char(15)
)

-- These are the user submitted set of answers 'answerid' for level 'gridid'.
-- Answers may be submitted in any order.
CREATE TABLE useranswers
(
answerid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
gridid smallint,
firstanswer char(15),
secondanswer char(15),
thirdanswer char(15),
fourthanswer char(15),
)

-- A user (userid) submits their answers which get stored as answerid.
-- This table shows the scores for each set of answerid's the user has submitted.
-- A high score table for both the individual user, and all users may be created using this table.
CREATE TABLE userscores
(
userid smallint,
answerid smallint,
mainmatches smallint,
bonusmatches smallint
)

The Test Data:
-- sample test data
-- 2 users userid's '1' and '2' each have two goes on level1 (gridid 1)

-- correct answers for gridid 1
INSERT INTO correctanswers (answer1, answer2, bonusanswer1, bonusanswer2) VALUES ('cat','dog','rabbit','elephant')

-- user submitted answers for gridid 1
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','rabbit','horse','cow')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','cat','elephant','horse')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'rabbit','cat','elephant','donkey')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'horse','cat','dog','sheep')

-- scores for users attempts - columns 3 and 4 needs calculating
INSERT INTO userscores VALUES (1,1,null,null) -- one main answer and one bonus answer, so null,null should be 1,1
INSERT INTO userscores VALUES (1,2,null,null) -- two main answers and one bonus answer, so null,null should be 2,1
INSERT INTO userscores VALUES (2,3,null,null) -- one main answer and two bonus answers, so null,null should be 1,2
INSERT INTO userscores VALUES (2,4,null,null) -- two main answers and no bonus answers, so null,null should be 2,0

I have included the correct new table values for the sample data - basically filling in the two null fields in the 'userscores' table.

I haven't used SQL much but from the little i know then i think the answer will include JOIN, COUNT and IN statements as part of the UPDATE statement...but i haven't a clue where to start with the order/logic etc.

I have looked for sample solutions myself but all the examples i have found so far are to do with exact matches between two tables, whereas in my scenario i need to know how many matches, irrelevant of the order.

Many thanks.
Roger

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-24 : 14:42:39

I'd strongly recommend that you reconsider your design. Will you really only ever (ever) have two correct answers and two bonus answers? What happens when you want to have one or three answers?
I'd suggest one table for the grids, then a separate table for the grid answers. Also a table for a user answer set and another for each answer. Counting the correct answers should then be a simple join from the user answer to the grid answer table based on the answer columns being equal, because each row in the result is a correct answer and incorrect answers won't join.

rog2054
Starting Member

5 Posts

Posted - 2007-11-24 : 18:47:26

Happy to change the design to help make it more flexible.

Can you provide a bit more detail about the tables you recommend, perhaps by giving the table creation statements? I understand that as a general rule more tables make a DB more efficient with less duplication, but i don't quite understand the tables you describe.

I think you're saying i need 4 tables rather than the 3 i currently have - possibly shrinking my existing tables in the process?

Many thanks for your help.

rog2054
Starting Member

5 Posts

Posted - 2007-11-25 : 08:14:08

Okay. Here is my new design. Ignoring my initial question about how to structure the UPDATE statement, could someone with more experience than me tell me how this design looks for the requirements?

-- correct answers for each grid
tGridAnswers
-- columns
gridid answertype answer
-- sample data for tGridAnswers
1 main cat
1 main dog
1 bonus rabbit
1 bonus elephant[/code]

-- user submitted answers
tUserAnswers
-- columns
answerid answer
--sample data for tUserAnswers
1 cat
1 dog
1 horse
1 rabbit
2 dog
2 cat
2 elephant
2 monkey

-- table to link which grid the answers are for
tAns2Grid
-- columns
answerid gridid
- sample data for Ans2Grid
1 1
2 1
3 1
4 1

-- the user scores for each set of answers they've provided
tUserScores
-- columns
answerid userid mainmatches bonusmatches
--sample data for tUserScores
1 1 null null
2 1 null null
3 2 null null
4 2 null null

Essentially i've rotated the tUserAnswers table from my original question, so each row only contains one answer.

I've also laid the tGridAnswers out in a similar way, and added a column to distinguish between main answers and the bonus ones.

I was in two minds about creating the additional table tAns2Grid - my alternative options where to include the gridid in either the tUserAnswers or tUserScores tables, but it seemed more efficient to store the relationship in a separate table to prevent repetition (might be called normalisation?)

Thoughts..?

rog2054
Starting Member

5 Posts

Posted - 2007-11-25 : 10:46:12

Nearly there...

I have the following query, which returns the answerid and the matching correct answers:

SELECT `tuseranswers`.answerid,`tuseranswers`.answer
  FROM    (   test1.tans2grid `tans2grid`
           INNER JOIN
              test1.tuseranswers `tuseranswers`
           ON (`tans2grid`.answerid = `tuseranswers`.answerid))
       INNER JOIN
          test1.tgridanswers `tgridanswers`
       ON (`tuseranswers`.answer = `tgridanswers`.answer)
          AND (`tans2grid`.gridid = `tgridanswers`.gridid)
			WHERE `tgridanswers`.answertype='main';

This result in:
answerid | answer
1 | dog
2 | dog
2 | cat
3 | cat
4 | cat
4 | dog

...which i can see gives the results i want, but not in the correct format. What i would like it to return is:
answerid | count of answers
1 | 1
2 | 2
3 | 1
4 | 2

How do i modify my SELECT statement to do this? I have tried add COUNT and/or GROUP BY but apparently thats an illegal combination of functions.

rog2054
Starting Member

5 Posts

Posted - 2007-11-25 : 11:41:40

So close...

My persistance is getting me through this! I now have the results i want from the SELECT query

SELECT `tuseranswers`.answerid, COUNT(`tuseranswers`.answerid) as Matches
  FROM    (   test1.tans2grid `tans2grid`
           INNER JOIN
              test1.tuseranswers `tuseranswers`
           ON (`tans2grid`.answerid = `tuseranswers`.answerid))
       INNER JOIN
       test1.tgridanswers `tgridanswers`
       ON (`tuseranswers`.answer = `tgridanswers`.answer)
          AND (`tans2grid`.gridid = `tgridanswers`.gridid)
			WHERE `tgridanswers`.answertype='main'
			GROUP BY `tuseranswers`.answerid,`tgridanswers`.answertype;

...now to turn this into an UPDATE statement to populate tUserScores.mainmatches with the results.

(a similar query will be required for the bonus matches; i don't think it's possible to combine that into 1 query, and i'm happy with 2 anyway...)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-25 : 22:15:36

You can use a FROM in an update to join in other tables and update from them.
Given that you can get the scores with the query you gave, it shouldn't be necessary to run an update to copy the scores to another table, just query the score for the user you want when you need them.

One more thing - based on your syntax it looks like you're using MySQL. This is a SQL Server forum, so while you may get generic SQL help here, you should probably check out a MySQL forum to get help with specific to MySQL - try http://forums.mysql.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources