By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Multiple instances for query!!

P: 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!
Dec 23 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,287
What are the fields in your tables?
Dec 23 '08 #2

Expert Mod 15k+
P: 31,491
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).
Dec 23 '08 #3

P: 2
Expand|Select|Wrap|Line Numbers
  1. SELECT Entry.site_cd,
  2.        Entry.site_desc,
  3.        Entry.class_cd,
  4.        Entry.class_desc,
  5.        Entry.lname_a,
  6.        Entry.fname_a,
  7.        Entry.student_cd,
  8.        Entry.lname_b,
  9.        Entry.fname_b,
  10.        Entry.mname,
  11.        Entry.form_cd,
  12.        Entry.rawscr,
  13.        Entry.scalescr,
  14.        Update.form_cd,
  15.        Update.rawscr,
  16.        Update.scalescr
  17. FROM Entry LEFT JOIN [Update]
  18.   ON (Entry.student_cd=Update.student_cd)
  19.  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.
Dec 23 '08 #4

Expert 100+
P: 1,287
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.
Dec 23 '08 #5

Expert Mod 15k+
P: 31,491
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!
Dec 23 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.