Find row id's which match two specific attributes

[John Linux]

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!

[pritaeas]

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).

[smantscheff]

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');

[pritaeas]

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

[smantscheff]

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.

[pritaeas]

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

[John Linux]

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