Results 1 to 14 of 14
  1. #1
    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)?

    Thanks in advance!

    Zlatko

  2. #2
    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. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    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.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    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. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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. #13
    Join Date
    Aug 2004
    Posts
    330
    you are right.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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