Hi, I have the following extract from a table called results.

StudentID Course Percent
1005 MAM1 63
1006 PHY2 55
1006 MAM1 71

I need to produce a list of student ID's WHO take MAM1 AND PHY2.
I first selected all rows where course is MAM1 OR PHY2, but then somehow I need to get rid of the student numbers that only take one of the courses! Please help!

Recommended Answers

All 6 Replies

If you select MAM1 or PHY2 then you can group the results by student-id and select only those who have two records (see HAVING).

select distinct studentid from results 
where studentid in (select studentid from results where course='mam1')
and   studentid in (select studentid from results where course='phy2');

Test:

drop table if exists results;
create table results (
  studentid integer,
  course varchar(10),
  percent integer
);
insert into results values 
(1001,'mam1', 63),
(1002,'phy2', 55),
(1002,'mam1', 99);
select distinct studentid from results 
where studentid in (select studentid from results where course='mam1')
and   studentid in (select studentid from results where course='phy2');

Since you are providing the solution, instead of letting him think first, this is what I meant (using your table definition):

SELECT studentid 
FROM results
WHERE course in ('MAM1', 'PHY2')
GROUP BY studentid
HAVING COUNT(*) = 2

I believe that John Linux did some thinking already before posting his problem.
Apart from that, your solution is technically more efficient while mine IMHO is better readable.
And what I do not like about your solution is that you have to change it in more than one place (WHERE and HAVING) when you add more conditions.

Glad to see different opinions, that is why I posted my solution.

Sidenote: suppose the courses you wanted to check for are rows in a separate table. The solution I showed can handle that by changing the hard-coded values to a sub-query.

Btw, nice to see you provided a full test example, +1

Hi Guys,

Thanks for the input,
thoroughly appreciated.

Worked it out earlier like this:

SELECT stuID
FROM results
WHERE stuID
IN (

SELECT stuID
FROM results
WHERE course = 'PHY2'
)
AND course = 'MAM1'

I assure you, I did a fair amount of thinking before posting on the forum, hence taking the time to do so. I didn't feel the need to track and post all my failueres!

Regards,
John

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.