Comparing two tables

1. Member
Join Date
Dec 1969
Posts
48

## Comparing two tables

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Let me show you why...

...you are having the problem.<BR><BR>Sample data, 4 records in each table.<BR><BR>-- QUESTIONS --<BR>qExamID :: qNumber :: qAnswer<BR> FOO :: 731 :: 1<BR> FOO :: 782 :: 4<BR> FOO :: 784 :: 1<BR> FOO :: 791 :: 2<BR><BR>-- STUDANSWERS --<BR><BR>ansExamID :: ansStudentID :: ansQuestionNo :: ansQuestion<BR> FOO :: Bob :: 731 :: 1<BR> FOO :: Bob :: 782 :: 1<BR> FOO :: Bob :: 784 :: 1<BR> FOO :: Bob :: 791 :: 1<BR><BR>Now, let&#039;s JOIN those tables as you did:<BR> ON (questions.qExamID = <BR>studAnswers.ansExamID<BR> AND <BR> questions.qAnswer = studAnswers.ansQuestion)<BR><BR>How many records do you think you should get?<BR><BR>Surprise! You will get *EIGHT* records!<BR><BR>Look why:<BR>Q: FOO :: 731 :: 1<BR>SA: FOO :: Bob :: 731 :: 1<BR>SA: FOO :: Bob :: 782 :: 1<BR>SA: FOO :: Bob :: 784 :: 1<BR>SA: FOO :: Bob :: 791 :: 1<BR>Q: FOO :: 782 :: 4<BR>Q: FOO :: 784 :: 1<BR>SA: FOO :: Bob :: 731 :: 1<BR>SA: FOO :: Bob :: 782 :: 1<BR>SA: FOO :: Bob :: 784 :: 1<BR>SA: FOO :: Bob :: 791 :: 1<BR>Q: FOO :: 791 :: 2<BR><BR>Do you see it yet? No?<BR><BR>What happens if you *REMOVE* the qNumber and ansQuestionNo fields *COMPLETELY*. How would that affect the JOIN?<BR><BR>ANSWER: *NOT AT ALL*. Because you NEVER USE those fields in the JOIN!<BR><BR>See the fix, now?<BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•