CODEWITHSUNDEEP
sqlms-accesscount
Qatrelnada
Qatrelnada

Reputation: 45

Mistake in Query

SELECT Student.S_ID, Student.First_Name, Student.Surname, 
       MAX(New_Models.Date_Issued) AS Last_Course_Date, 
       MAX(New_Models.Issue) AS Last_Issue, 
       MAX(New_Models.Model_ID) AS Last_Model_ID,
       Student.Course_Level AS No_Training_Courses,
       **COUNT(Exam_Student.Exam_ID) AS Final_Exam_Level**
FROM New_Models, Model_Student, Student, Exams, Exam_Student
WHERE New_Models.Model_ID=Model_Student.Model_ID 
  AND Student.S_ID=Model_Student.S_ID
  AND (Student.S_ID)=Exam_Student.S_ID
  AND ((Exams.Exam_ID)=Exam_Student.Exam_ID)
  AND (Exams.Date_Taken)<=#12/31/2010#
GROUP BY Student.S_ID, First_Name, Surname, S.Course_Level
ORDER BY Student.S_ID, MAX(New_Models.Model_ID) DESC;

All the result appear as required except the COUNT command. When comparing with actual result whihc should come only the first cell differs and is wrong. It should be counting how many exams has the student done.

I can answer any questions, will be waiting for your help.

SELECT 
    Student.S_ID, 
    Student.first_name, 
    Student.surname, 
    MAX(new_models.date_issued) AS last_course_date, 
    MAX(new_models.issue) AS last_issue, 
    MAX(new_models.model_id) AS last_model_id, 
    Student.course_level AS no_training_courses 
FROM 
    new_models, 
    Student, 
    Model_Student 
WHERE 
    new_models.model_id = Model_Student.model_id 
    AND Student.S_id = Model_Student.S_id 
    AND *strong text*Exam_ID IN ( 
            SELECT COUNT(Exam_Student.Exam_id) AS Final_Exam_Level 
            FROM 
                Exams, 
                Exam_Student, 
                Student 
            WHERE 
                Student.S_ID = Exam_Student.S_ID 
                AND exams.date_taken <=#12/31/2010# 
            Group by Student.S_ID) 
GROUP BY 
    Student.S_ID,
    first_name, 
    surname, 
    Student.Course_level 
ORDER BY 
    Student.S_id, 
    MAX(new_models.model_id) DESC;

I cannot use INNER JOIN so, I was wondering that something like the above can be done. However, I dont know how to do the subquery!

Upvotes: 1

Views: 86

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

One approach that may help is to simply create a subquery on the Counts and then join to that.

I also recommend using ANSI-92 style joins

SELECT student.s_id, 
       student.first_name, 
       student.surname, 
       MAX(new_models.date_issued) AS last_course_date, 
       MAX(new_models.issue)       AS last_issue, 
       MAX(new_models.model_id)    AS last_model_id, 
       student.course_level        AS no_training_courses,
       Examqry.ExamCount 
FROM   new_models 
       INNER JOIN model_student ON  new_models.model_id = model_student.model_id 
       INNER JOIN student ON student.s_id = model_student.s_id 
       INNER JOIN (SELECT COUNT(Exam_id) ExamCount , 
                                         s_id 
                           FROM exams Group by s_id
                          Where exams.date_taken <=#12/31/2010#) examqry
       ON student.s_id = examqry.s_id

GROUP  BY student.s_id, 
          first_name, 
          surname, 
          s.course_level 
ORDER  BY student.s_id, 
          MAX(new_models.model_id) DESC;

Upvotes: 3

Hogan
Hogan

Reputation: 70523

I'm guessing without seeing example data but maybe try this, change

AND (Student.S_ID)=Exam_Allocation.S_ID
AND ((Exams.Exam_ID)=Exam_Allocation.Exam_ID)

to

AND Student.S_ID=Exams.Exam_ID
AND Exams.Exam_ID=Exam_Allocation.Exam_ID

If this does not help you will need to post some example data with expected and actual results for someone to help.

Upvotes: 0

Related Questions