I have a table of buiding inspections (insmstr). Each inspection
is done once a year. The next inpsection is done either 12 or 24
months from the previous inspection. The inspection can occur
in any month and can be early or late. (Almost sounds like real
life.) It has a foreign key of imbdkey which links the inspection
record to the building. I have another table of buildings
(buildings). The primary key is building number (bdkey).


What I am trying to do is to place the last 3 years inspections
into one record in one select statement. What I propose to do
is:

SELECT bd.bdkey, bd.bdname, im.iminspdt, im.iminsptype, ;
( SELECT im.iminspdt FROM insmstr imyr WHERE ;
imyr.iminspdt >= {01/01/2000} AND imyr.iminspdt ;
<= {12/31/2000} AND imyr.imbdkey = bd.bdkey ) ;
as YR1date , ;
( SELECT im.iminspdt FROM insmstr imyr WHERE ;
imyr.iminspdt >= {01/01/2001} AND imyr.iminspdt ;
<= {12/31/2002} AND imyr.imbdkey = bd.bdkey ) ;
as YR2date , ;
FROM building bd INNER JOIN insmstr im ON ;
bd.bdkey = im.imbdkey WHERE im.iminspdt >= {01/01/2002}

Needless to say, the above generates an error.

I know I can achieve the results I want in a lot of ways, but I believe
that a single SELECT statement may be the fastest way to get all of the
data I need. The above is a simplfied version of the final statement
which actually joins about 6 tables and works quite well as long as I
leave out the attempt to get the year 2000 and 2001 columns. I figure if
I get the dates in one pass without calling any functions or multi-cursor
scenerios it would be the fastest way to go. I'm open to dialogue,
a little mentoring, or even the feedback that it can't be done this way.

Thanks,

sjwilson