Multiple instances for query!! | Newbie | | Join Date: Dec 2008
Posts: 2
| | |
I have a couple of tables with test results in both of them. They are pre and post test which need to be displayed by a eventually. The relationship I used for my tables A and B are one to many. Everything gets sorted out by School and Class Number. The problem comes when if a student from table A has taken 2 different test in the same class. The query lists 4 instances for the same student instead of 2. How can I solve this issue??? Is it a problem in the relationships or am I querying the tables wrong! PS. I am using a LEFT JOIN for the tables!
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,175
| | | re: Multiple instances for query!!
What are the fields in your tables?
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Multiple instances for query!!
This generally happens when multiple records (A) on the left side of a JOIN match multiple records (B) on the right.
Naturally you get A x B result records.
You need to look at your JOIN (We can't as you didn't include it in the question).
| | Newbie | | Join Date: Dec 2008
Posts: 2
| | | re: Multiple instances for query!!
- SELECT Entry.site_cd,
-
Entry.site_desc,
-
Entry.class_cd,
-
Entry.class_desc,
-
Entry.lname_a,
-
Entry.fname_a,
-
Entry.student_cd,
-
Entry.lname_b,
-
Entry.fname_b,
-
Entry.mname,
-
Entry.form_cd,
-
Entry.rawscr,
-
Entry.scalescr,
-
Update.form_cd,
-
Update.rawscr,
-
Update.scalescr
-
FROM Entry LEFT JOIN [Update]
-
ON (Entry.student_cd=Update.student_cd)
-
AND (Entry.class_cd=Update.class_cd)
Both tables contain the a set of tests taken at different dates! The pre has to match the post for progress issues! Student_cd is the student ID and class_cd is the class ID.
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,175
| | | re: Multiple instances for query!!
Each record in table A is going to have X rows in the resulting table, where X is the number of matches in table B.
So if a student&class match in 2 rows in table A and 2 rows in table B, you will get 4 rows in the result.
All these are valid results based on the information given, so you may need to add more data to differentiate the 2 tests in the same class for the same student.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Multiple instances for query!!
Apart from adding the [ CODE ] tags to your post, I also reformatted the SQL because - well, because you hadn't. Free-form SQL is fine for a computer to read. Being human we rather prefer something legible.
What is important here in your SQL, is not so much the specific SQL used (although that may help further down the line) but the structure of the data in each of the two tables. This is what both Chip and I have mentioned already. If you don't want to match multiple records on the left with multiples on the right (I'm guessing not as you complained about too many records in the output) then you need to consider how properly to JOIN the tables within the query (SQL). At this point we don't have enough information about your data to tell you how this should be done. The problem you're reporting certainly indicates that what you are currently using is not correct though.
I hope that helps and Welcome to Bytes!
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
