How do I rewrite my query to be based on the existence of a given foreign key, rather than the number of times that key occurs?
In the most general of cases, I have a query like below:
SELECT tutor_school.name, count(*), tutor_school.schoolid
FROM tutor_school, tutor_attends, tutor_tutors_in
WHERE
tutor_school.schoolid = tutor_attends.schoolid and
tutor_school.schoolid in ('1', '2', '3') and
tutor_attends.userid=tutor_tutors_in.userid
group by tutor_school.schoolid LIMIT 0, 10
In essence, I want:
Name of School, Number of Students attending that school that tutor in any subject, Schoolid
What I'm actually getting is
Name of School, sum of all subjects taught by students at that school, schoolid -- in other words, if student 1 tutors 3 subjects, student 2 tutors 5, then instead of returning 2 I get 8!
I've realized that the issue is with the following statement:
tutor_attends.userid=tutor_tutors_in.userid
This isn't checking the existence of a given foreign key in a remote table, it's giving a r开发者_Python百科esult for each instance of that key.
What I'm trying to figure out is how to bind it to limit it to simply the existence of the given key, not the number of times that key occurs. I know I've seen a case similar to this in my SQL class, but I can't remember what the solution was.
Andrew is correct and here's a concrete example of his solution (had to make assumptions on your table design):
SQL> SELECT * FROM tutor_school;
SCHOOLID NAME
---------- --------------------
1 School A
2 School B
3 School C
SQL> SELECT * FROM tutor_attends;
USERID SCHOOLID
---------- ----------
1 1
2 1
3 2
SQL> SELECT * FROM tutor_tutors_in;
USERID SUBJECT
---------- --------------------
1 Math
1 Science
1 English
2 English
3 Math
SQL> SELECT tutor_school.name, COUNT(DISTINCT tutor_tutors_in.userid)
2 FROM tutor_school, tutor_attends, tutor_tutors_in
3 WHERE tutor_school.schoolid=tutor_attends.schoolid
4 AND tutor_attends.userid=tutor_tutors_in.userid
5 GROUP BY tutor_school.name
6 /
NAME COUNT(DISTINCTTUTOR_TUTORS_IN.USERID)
-------------------- -------------------------------------
School A 2
School B 1
First, you should learn the ANSI syntax for JOINs. Using just the WHERE
clause is deprecated. Leaving that aside, I can think of a solution without even using EXISTS
.
SELECT tutor_school.name, count(DISTINCT tutor_attends.userid), tutor_school.schoolid
FROM tutor_school, tutor_attends, tutor_tutors_in
WHERE
tutor_school.schoolid = tutor_attends.schoolid and
tutor_school.schoolid in ('1', '2', '3') and
tutor_attends.userid=tutor_tutors_in.userid
group by tutor_school.schoolid LIMIT 0, 10
Where I am a little confused about your schema is that I see join tables, but no plain-old tutor
table. It might be easier to conceptualize the query with one.
The relational operator you require is semijoin (rather than join). From your natural language statement of the requirement, I think using EXISTS
would be most appropriate
e.g.
SELECT tutor_school.name, count(*), tutor_school.schoolid
FROM tutor_school, tutor_tutors_in
WHERE
tutor_school.schoolid = tutor_attends.schoolid and
tutor_school.schoolid in ('1', '2', '3') and
exists (
SELECT *
FROM tutor_attends
WHERE tutor_attends.userid = tutor_tutors_in.userid
)
group by tutor_school.schoolid LIMIT 0, 10
精彩评论