I got stuck in SQL select that has exactly multiple values

Question

Table name: test
id - student - class
1 - Jane - A
2 - David - B
3 - Jane - C
4 - Nick - A
5 - Nick - B
6 - Smith - C
----------------

Now I want to find out Student name that enrolled both A and C class (must be A and C) the result will show Jane because she enrolled in both A and C

p/s: Im using MS - ACCESS 2007

Please provide your DBMS. Or the question will likely be deleted as unclear. — Erwin Brandstetter, Jan 15 '15 at 08:17

score 2 · Answer 1 · edited May 23 '17 at 12:11

2

This is a case of relational division. We have assembled an arsenal of techniques here:

How to filter SQL results in a has-many-through relation

Should be among the fastest solutions:

SELECT student
FROM   test t1
JOIN   test t2 USING (student)
WHERE  t1.class = 'A'
AND    t2.class = 'C';

MS Access has a rather basic implementation of SQL. And it uses double quotes for strings (which contradicts the standard):

SELECT test.student
FROM   test
INNER  JOIN test AS t2 ON test.student = t2.student
WHERE  test.class = "A"
AND    t2.class = "C";

DISTINCT (or grouping of the result) is only needed if the combination (student, class) is not unique - which it should be in most use cases.

edited May 23 '17 at 12:11

Community

1
1

answered Jan 15 '15 at 08:13

Erwin Brandstetter

605,456
145
1,078
1,228

There's no t1.student available in the select-list here, thanks to USING(student). The columns available are t1.id, t1.class, t2.id, t2.class, and student. – jarlh Jan 15 '15 at 08:18
It won't work. you even not mention about the 'class' – Minh Trương Jan 15 '15 at 08:19
@MinhTrương: Sorry, typo, fixed. – Erwin Brandstetter Jan 15 '15 at 08:20
@jarlh: `t1.student` would still be available and correct (at least in Postgres), even though the table qualification is not *necessary* after joining with `USING (student)`. – Erwin Brandstetter Jan 15 '15 at 08:20
@ErwinBrandstetter, according to ANSI SQL it's not. Since no dbms specified here it's better to give a dbms independent answer. – jarlh Jan 15 '15 at 08:22
Great, personally I don't really like JOIN USING just because of this somewhat odd behavior. (But still it's better than NATURAL JOIN...) – jarlh Jan 15 '15 at 08:24
TRIED, but syntax Error at From <<< Im using MS - ACCESS – Minh Trương Jan 15 '15 at 08:29
@jarlh: In a self-join `USING` is reasonably safe. – Erwin Brandstetter Jan 15 '15 at 08:29
@MinhTrương: Provided a simple form that should work in Access. – Erwin Brandstetter Jan 15 '15 at 08:32
I tried as your MS Access syntax but still Syntact Error. Could u try it by your access ? – Minh Trương Jan 15 '15 at 08:34
in Access, I just create a table name 'test' and 3 column 'id', 'student', 'class' then I filled records by hand one by one as above... This just for test. Because my real Database is bigger than this. I just take one simple to test it – Minh Trương Jan 15 '15 at 08:35
@MinhTrương: Must have been the quotes. Access uses double quotes for strings. Should work now. – Erwin Brandstetter Jan 15 '15 at 08:40
yes, the final one can work. it still worked with single quote. And it should have distinct after select to get one Name. Anyway thanks a lots. It easy to understand. Thanks :P – Minh Trương Jan 15 '15 at 09:03

juergen d · Accepted Answer · 2015-01-15T08:30:07.020

0

Group by the students and select only classes Aand C. then take only those students having both entries

select student
from test
group by student
having sum(iif(class = 'A',1,0)) > 0
and sum(iif(class = 'B',1,0)) > 0

edited Jan 15 '15 at 08:30

answered Jan 15 '15 at 08:07

juergen d

201,996
37
293
362

Thanks but it show Syntax Error (missing operator) in query expression 'count (distinct class) = 2' – Minh Trương Jan 15 '15 at 08:15
Hmmm. What DB engine do you use? – juergen d Jan 15 '15 at 08:17
Im using MS ACCESS 2007 – Minh Trương Jan 15 '15 at 08:25
Oh. Access does nort support `distinct` AFAIK. – juergen d Jan 15 '15 at 08:28
Perfect dude... it worked... Can u explain me details abt these one. I really at basic SQL only. appriciated that ! – Minh Trương Jan 15 '15 at 08:37
You group by the students. Then you can use the `having` clause to look at all records for each student. The students you want to select need to have classes A and B. Since you look at multiple records of a student in a group you need to use aggregate functions like ´sum()`. In that sum is a case that sums up the number of correct classes. Do it doe A and B – juergen d Jan 15 '15 at 08:41
`if(class='A') then 1 else 0` That condition return 1 for true and 0 for false. The `sum()` sums up those true condtions. At least once (`sum>0`) this must be true for a student for each class – juergen d Jan 15 '15 at 09:03
appriciated dude ! Thanks a lots :D – Minh Trương Jan 15 '15 at 09:17

Amol Bavannavar · Answer 3 · 2015-01-15T08:31:35.037

0

Take name's of A Class students & C Class Students & do Intersection on these result set's

select distinct
 a.student
from
 test a
 inner join test b on a.id = b.id
 where a.class = 'A' and b.class= 'C'

edited Jan 15 '15 at 08:31

answered Jan 15 '15 at 08:14

Amol Bavannavar

2,062
2
15
36

could u replace a, b by student, class... im confusing sorry my bad – Minh Trương Jan 15 '15 at 08:39

score 0 · Answer 4 · answered Jan 15 '15 at 08:34

0

This is one way:

SELECT DISTINCT STUDENT 
FROM TEST T 
WHERE 
EXISTS (SELECT NULL FROM TEST T1 WHERE T.STUDENT = T1.STUDENT AND T1.CLASS='A') AND
EXISTS (SELECT NULL FROM TEST T2 WHERE T.STUDENT = T2.STUDENT AND T2.CLASS='C');

answered Jan 15 '15 at 08:34

Lord Peter

3,433
2
33
33

I got stuck in SQL select that has exactly multiple values

4 Answers4