0

I am trying to make a query to find all details of students interviewed more than once

I have gotten the results successfully using:

SELECT S.StudNo, S.StudLName, S.StudFName, S.StudMobile, S.City, S.DateEnrolled, S.ProgNo, S.AmountDue, S.Gender
FROM STUDENT AS S INNER JOIN STUDENT_INTERVIEW AS SI ON S.StudNo = SI.StudID
WHERE
(SELECT COUNT(SI.StudID)
FROM STUDENT_INTERVIEW)
GROUP BY S.StudNo, S.StudLName, S.StudFName, S.StudMobile, S.City, S.DateEnrolled, S.ProgNo, S.AmountDue, S.Gender
HAVING COUNT(SI.StudID) > 1;

But it seems excessively long.. I am trying to rewrite it to make it less wordy although am unable to get the correct results. I have been trying to use more sub queries to not have to use GROUP BY. When I do so I get the results for all students, not the specific 2 I'm after

SELECT *
FROM STUDENT
WHERE StudNo IN
(SELECT StudID
FROM STUDENT_INTERVIEW
WHERE 
(SELECT COUNT(StudID)
FROM STUDENT_INTERVIEW
HAVING COUNT(StudID) > 1;))
Puff
  • 1
  • 3
  • Did you copy first query wrong into your post? It is syntactically incorrect. The `WHERE` clause does not evaluate to any TRUE/FALSE. – Parfait Aug 17 '17 at 02:36

1 Answers1

1

Your first query is a good start. Here is an improvement:

SELECT S.*
FROM STUDENT AS S
WHERE S.StudNo IN (SELECT SI.StudId
                   FROM STUDENT_INTERVIEW as SI
                   GROUP BY SI.StudId
                   HAVING COUNT(*) > 1
                  );

The outer query needs neither the JOIN nor the GROUP BY. You can basically do all the work in the subquery.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much, I was working off the notes I get from class which I don't think explain anything too well. If you are happy to explain, I am curious as to how COUNT(*) works in this case? I was worried to use it assuming it'd count everything and not narrow down the StudID. Since it is the only thing in SELECT does access know to only count the StudID? – Puff Aug 17 '17 at 00:55
  • `COUNT(*)` counts the number of matching rows. It should be equivalent to counting the primary key. – Gordon Linoff Aug 17 '17 at 00:57
  • Thanks for explaining – Puff Aug 17 '17 at 01:02