In need for a solution for practical problem

zlatko99 · #1 (**permalink**) 08-06-04, 21:32

zlatko99 · #2 (**permalink**) 08-07-04, 09:00

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

LKBrwn_DBA · #3 (**permalink**) 08-08-04, 12:45

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.

r937 · #4 (**permalink**) 08-08-04, 18:49

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

zlatko99 · #5 (**permalink**) 08-08-04, 21:27

Rudy,

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

Thank you,
Zlatko

r937 · #6 (**permalink**) 08-08-04, 23:45

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

zlatko99 · #7 (**permalink**) 08-09-04, 21:23

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

r937 · #8 (**permalink**) 08-09-04, 21:27

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

orababa@kshema · #9 (**permalink**) 08-10-04, 00:59

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

r937 · #10 (**permalink**) 08-10-04, 06:41

Quote:

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

urquel · #11 (**permalink**) 08-16-04, 12:17

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.