Problem using joins. (V.V.Urgent)

joydip_kanjilal · #1 (**permalink**) 05-03-04, 08:25

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.

r937 · #2 (**permalink**) 05-03-04, 09:31

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    
)

joydip_kanjilal · #3 (**permalink**) 05-09-04, 02:56

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.

r937 · #4 (**permalink**) 05-09-04, 04:04

well, i am sorry for giving you a mysql solution in the mysql forum for what you clearly indicated was a mysql problem

Internet Services

Web Development

Digital Marketing

Consumer Tech