January 23, 2007 at 10:20 am
I have two tables, one that represents Files and one the represents File Events (open, close, etc.).
File Table Columns
FileID, Name
Events Table Columns
EventID, FileID, EventType, EventTime
I need to obtain the Closed and Open events as column values for a selected group of files. The obvious way to do this is:
Select w.*, c.EventTime as Closed, o.EventTime as Opened
From Files f
join Events c on w.FileID= c.FileID
Join Events o on w.FileID= o.FileID
Where f.Name LIKE ‘foo%’ and c.EventType = “Closed” and o.EventType = “Opened”
Is there any way to cut this down to a single join?
January 25, 2007 at 2:11 am
Nothing wrong with that second join. But if you really insist, this is a way to do it.
create table files
(
fid int,
fname varchar(30)
)
insert into files values(1,'file1')
insert into files values(2,'file2')
insert into files values(3,'file3')
insert into files values(4,'file4')
create table events
(
eid int,
fid int,
etype varchar(10),
etime datetime
)
insert into events values(1,2,'open','2007-01-25 15:01:00')
insert into events values(2,3,'close','2007-01-25 15:02:00')
insert into events values(3,4,'open','2007-01-25 15:03:00')
insert into events values(4,4,'close','2007-01-25 15:04:00')
select fname,max(opentime),max(closetime)
from
(
select fname,
case when e.etype='open' then etime else null end opentime,
case when e.etype='close' then etime else null end closetime
from files f
join events e on f.fid=e.fid
) t
group by fname
January 27, 2007 at 9:48 am
Great. I know I would get a good answer here.
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply