Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: SQL query...please help

    Hi People:
    I'm trying to write a query against 2 tables, the details of which are given below.

    TABLE CLASSTIMES : coursename starttime1 endtime1 starttime2 endtime2
    phy101 1200 1300 3500 3600
    chem250 1350 1400 6500 7000

    TABLE STUDENTTIMES : studentname course starttime1 endtime1 starttime2 endtime2

    x course1 1000 1100 3400 3500
    x course2 1300 1400 6400 6450
    x course3 1200 1250 3600 3730
    y course... 1100 1150 3200 3300
    y course... 0950 1030 3650 3700

    Each row in table 'CLASSTIMES' give the classtimes of that particular course on day1
    (read starttime1 and endtime1) and day2 (starttime2 and endtime2) of a week.

    Each row in table 'STUDENTTIMES' give the timings of the courses that the student is currently
    enrolled for. starttime1 & starttime2 correspond to the times of the first & second days of that
    class in a week.

    Now, I want to write a query that takes into account starttimes and endtimes for all the courses
    for a student from table 'STUDENTTIMES', and tell whether
    the student is available to enroll in a given course from table 'CLASSTIMES'. Please note the columns
    'COURSENAME' from table 'CLASSTIMES' column 'COURSE' from table 'STUDENTTIMES' are not the same. So,
    we can not compare these two. We can only compare the times.

    I don't know PL/SQL, so I can't use stored procedures and all. Can somebody tell me how to write the query using SQL? I appreciate your help.


  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    The columns 'ST1' and 'ST2' represent Day 1 and Day 2 respectively.

    Create or replace view ST_RESULT AS
    select st_name, name, start_time, end_time, start_time2, end_time2, st1, st2, count(*) AS COUNT
    (select As ST_NAME, s.start_time As ST_START_TIME, s.end_time As ST_END_TIME, s.start_time2 As
    ST_START_TIME2, s.end_time2 As ST_END_TIME2,, c.start_time, c.end_time, c.start_time2, c.end_time2,
    (case WHEN (c.start_time NOT between s.start_time+1 AND s.end_time-1) AND
    (c.end_time <= s.start_time OR c.start_time >= s.end_time) THEN 'TRUE' ELSE 'FALSE' END) As ST1,
    (case WHEN (c.start_time2 NOT between s.start_time2+1 AND s.end_time2-1) AND
    (c.end_time2 <= s.start_time2 OR c.start_time2 >= s.end_time2) THEN 'TRUE' ELSE 'FALSE' END) As ST2
    from students s, courses c)
    group by st_name, name, start_time, end_time, start_time2, end_time2, st1, st2
    having st1 = 'TRUE' OR st2 = 'TRUE';

    select *
    from st_result V
    where (ST1='TRUE' AND COUNT = (select count(distinct start_time) from students where name = V.st_name)) OR
    (ST2='TRUE' AND COUNT = (select count(distinct start_time2) from students where name = V.st_name));
    Last edited by r123456; 01-27-04 at 08:37.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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