Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: Problem using joins. (V.V.Urgent)

    I am using My SQL 4.0. It doesnot support nested sub queries.
    Please use joins.


    The following is the structure of the table.


    CREATE TABLE `timeslot` (
    `timeSlotID` int(12) NOT NULL auto_increment,
    `tableID` varchar(12) default '',
    `startTime` varchar(20) default '00:00:00',
    `endTime` varchar(20) default '00:00:00',
    `dayOfWeek` varchar(100) default '0',
    `duration` int(12) default '0',
    `status` varchar(10) default 'N',
    `companyID` int(12) default '0',
    PRIMARY KEY (`timeSlotID`)
    ) TYPE=InnoDB;


    I require to display the startTime of the min timeSlotID and the endTime of the max timeSlotID where dayofweek=1. I want the slotuion using joins but not by creating any temporary tables. Consider that there are a lot of records for dayofweek = 1.

    The output that I require is like the following:---

    StartTime MinTimeSlotID EndTime MaxTimeSlotID
    8:00 49 21:45 64

    Thanx in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    (
    select 'MIN'      as resulttype
         , startTime  as thetime
         , timeSlotID
      from timeslot
    order 
        by timeSlotID   
    limit 1    
    )
    union all
    (
    select 'MAX'     
         , endTime    
         , timeSlotID
      from timeslot
    order 
        by timeSlotID desc  
    limit 1    
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    21

    Join Problem. (Please suggest an alternative)

    Thanx a lot for the answer. Please suggest an alternative without having to use the limit clause. This is because LIMIT is allowed in My SQL but not in other databases.

    Thankx a lot once again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i am sorry for giving you a mysql solution in the mysql forum for what you clearly indicated was a mysql problem
    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
  •