#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0

    SQL query taking forever


    I don't understand much about the grouped "AND" and "OR", but here is my query which just does not seem to run:

    SELECT distinct s.student_id, classresults_2 t1, classresults_8 t2,schedule c
    WHERE ( c."Teacher Last Name"='ADAMS' OR c."Teacher Last Name"='KIRK' ) AND (s.student_id=t1.student_id OR s.student_id=t2.s.student_id) AND (s.student_id=c.student_id);

    Basically, I want the student_id from the two tables: classresults_2 and classresults_8, from students who'se teacher last name is "ADAMS" or "KIRK".

    It hangs on the (s.student_id=t1.student_id OR s.student_id=t2.student_id)

    Here, I'm just asking to find students who have their student_id in classresults_2 OR classresults_8. s.sid is the master student id table, so we're finding students IDs which are also in the master student ID list.

    Is there anything wrong with the query?

    Thank you
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Um... I don't see a FROM clause in your query. Also, you are using the alias "s" for a tablename, but nowhere are you creating this alias. Or is "s" an actual tablename? Also, what is "s.student_id=t2.s.student_id"? (I'm assuming that's a typo, but I'm still surprised your query didn't produce a large error message.)

    Maybe you want something like this (I'm guessing you have a table called something like "students"):
    Code:
    SELECT distinct s.student_id FROM students s, classresults_2 t1, classresults_8 t2,schedule c
       WHERE ( c."Teacher Last Name"='ADAMS' OR c."Teacher Last Name"='KIRK' ) 
       AND (s.student_id=t1.student_id OR s.student_id=t2.student_id) 
       AND (s.student_id=c.student_id);
    Although, I'm not sure about "s.student_id=t1.student_id OR s.student_id=t2.student_id". I would think the join condition needs to be specified for both, not one OR the other. To simplify things, you might want to just do a natural join between these tables, since they all use the same key (student_id). Maybe you could rewrite the query this way:

    Code:
    SELECT distinct student_id FROM students 
       NATURAL JOIN classresults_2 
       NATURAL JOIN classresults_8 
       NATURAL JOIN schedule 
    WHERE schedule."Teacher Last Name"='ADAMS' AND schedule."Teacher Last Name"='KIRK'
    (OR in the WHERE clause won't work, because "Teacher Last Name" cannot be two values at once anyway. Again, I'm assuming here, but it looks like you want all rows that match both of those conditions, otherwise, just put the one you want to match)

    I love using natural join syntax when possible, because it expresses relationships so well, without pushing them into the WHERE clause. This leaves your WHERE clause clear for the specific item you are searching.

    Again, this is all just speculation because I don't know your exact table definitions, but give it a try, and then let us know if you have any other problems. If there is a specific performance problem, it will probably just be a question of indexing, or re-thinking database design.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Code:
    SELECT distinct student_id FROM students 
       NATURAL JOIN classresults_2 
       NATURAL JOIN classresults_8 
       NATURAL JOIN schedule 
    WHERE schedule."Teacher Last Name"='ADAMS' AND schedule."Teacher Last Name"='KIRK'
    Thank you for your reply and suggestion. I tried it but it did not work for what I wanted to do. It always returned 0 rows...

    What I want to find is: all student_ids who are in classresults_2 OR classresults_8 and from those student_ids, find out if they are taught by ADAMS or KIRK (and this is within the schedule table).

    All tables do have the student_id and there exists a student_id index in each table, and I have done vacuum analyze.

    I have devised this:

    Code:
    SELECT distinct s.student_id, s."Building Code" from students s, schedule c 
    WHERE  ( c."Teacher Last Name"='ADAMS'
    OR  c."Teacher Last Name"='KIRK' ) 
    AND s.student_id=c.sid AND 
    s.student_id IN 
    (SELECT student_id FROM classresults_2 
    UNION ALL SELECT student_id from classresults_8);
    It works.

    However, this takes a long time to query and the load on the server does spike.

    Is there any way to make that query more efficient?

    Thank you very much for all your help.
    Last edited by onefix; November 20th, 2003 at 02:53 AM.

IMN logo majestic logo threadwatch logo seochat tools logo