Re: Query problem

[Bill Karwin]

Here's a solution that seems to work. This sort of problem isn't easy.

CREATE TABLE `rents` (
`customerid` int(11) default NULL,
`startdate` date default NULL,
`enddate` date default NULL,
`vehicleid` int(11) default NULL
)

INSERT INTO rents VALUES
(2, '2006-04-04', '2006-04-11', 6),
(3, '2006-04-06', '2006-04-30', 6),
(4, '2006-04-04', '2006-04-11', 8);

select r2.customerid, r2.vehicleid, r2.startdate as s2, r1.startdate as s1
from rents as r1 inner join rents as r2
on (r1.vehicleid = r2.vehicleid and r1.customerid <> r2.customerid
and (r1.startdate > r2.startdate and r1.startdate <= r2.enddate))
union all
select r2.customerid, r2.vehicleid, r1.enddate as s2, r2.enddate as s1
from rents as r1 inner join rents as r2
on (r1.vehicleid = r2.vehicleid and r1.customerid <> r2.customerid
and (r1.enddate < r2.enddate and r1.enddate >= r2.startdate))
union all
select r1.customerid, r1.vehicleid, r1.startdate, r1.enddate
from rents as r1 left outer join rents as r2
on (r1.vehicleid = r2.vehicleid and r1.customerid <> r2.customerid
and (r1.startdate between r2.startdate and r2.enddate or r1.enddate between r2.startdate and r2.enddate))
where r2.customerid is null

Regards,
Bill K.