# Thread: In need for a solution for practical problem

1. Registered User
Join Date
Dec 2003
Posts
8

## Unanswered: In need for a solution for practical problem

Hi,

I'm in need for a solution for a very practical problem.
(Despite the following example, this is NOT a homework)!!!

Let's say, I have 2 tables: the first one populated with students taking courses, and, the second one populated with students taking classes at one or more campuses...

+========================+
| student | subject |
+==========+=============+
| student1 | physics |
| student1 | chemistry |
| student1 | math |
| student2 | english |
| student2 | math |
| student3 | history |
| student3 | chemistry |
| student3 | programming |
| student3 | physics |
| student3 | math |
| ..... | ..... |
+------------+-----------------+

+========================+
| student | campus |
+==========+=============+
| student1 | north |
| student1 | west |
| student2 | south |
| student2 | north |
| student2 | east |
| student3 | south |
| ..... | ..... |
+-------------+----------------+

The question I want to "ask" the database is:
"Give me all students where each student takes at least Physics and Math, and takes classes at the North campus.". This query should give me only the student1, because student1 takes Physics and Math (among the others) and, also, takes subjects at the North campus.

How should I assemble the query for this task? What if I have many tables (many conditions)?

Zlatko

2. Registered User
Join Date
Dec 2003
Posts
8

## In need for a solution for practical problem

Well, I forgot to mention that the solution query should not contain subselects (because the query should work on MySql 3.23 which doesn't support subselects).

Zlatko

3. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
The solution is so simple that you may want to try and solve it yourself !
You say this is not HOMEWORK, however it is basic SQL!

HINT: Try using a JOIN.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
it's not that easy

using only joins, you would need to join three tables (two from a "self-join")

you can also do it with a join of just the two tables involved, but also a GROUP BY

5. Registered User
Join Date
Dec 2003
Posts
8
Rudy,

can you tell me, please, how exactly to build the query using join on only 2 tables?

Thank you,
Zlatko

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```select s.student
from courses s
inner
join classes c
on s.student = c.student
where s.subject in ('physics','math')
and c.campus = 'north'
group
by s.student
having count(distinct subject) = 2```

7. Registered User
Join Date
Dec 2003
Posts
8
I agree with that query, but what if I want to set more conditions for the campuses as well? For example, how would you build the query having this condition:

.....
where s.subject in ('physics','math')
and c.campus in ('north', 'south', 'east')
.....

I tried something with this new one, but failed...

Zlatko

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
i would try this --
Code:
``` where s.subject in ('physics','math')
and c.campus in ( 'north','south','west' )
group
by s.student
having count(distinct subject) = 2
and count(distinct campus) = 3```

9. Registered User
Join Date
Nov 2003
Location
Bangalore,India
Posts
51
I disagree with all of them ...
IN clause will show result if student takes either of the subjects. I guess 2 subjects are mandatory. Its difficult to find some searches like this when u have data in Vertical rather than horizontal....

Please consider changing the structure of the table...
Cheers

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by orababa@kshema
IN clause will show result if student takes either of the subjects
yes, and HAVING ensures student takes both

the table structure is fine

11. Registered User
Join Date
Aug 2004
Posts
330
incorrect, HAVING would allow Physics and one other course or Math and one other course to qualify for the result-set. You are looking for Math AND Physics.

12. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Urquel, you are wrong. Rudy's query ensures that:
1) only Maths and Physics are included:
Code:
` where s.subject in ('physics','math')`
and
2) 2 different subjects are included:
Code:
`having count(distinct subject) = 2`
The only way both conditions can be true is if the student takes both Maths and Physics!

13. Registered User
Join Date
Aug 2004
Posts
330
you are right.

14. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
thanks tony

urquel, don't worry about it, i've done the same

sql can be very deceptive, no?

especially in the area of ANDs and ORs and HAVINGs...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•