limit rows in join

Hi all -
I have been trying to figure out if this can be done in one query, or if I
need to break it out into multiple queries. The rows are for a report - so
subreports may be the direction I need to go.
I have an sql statement with 5 joins in it - three of the joins are no
problem - I need the fourth join (row) to get to the fifth join (table). Th
e
problem is that the fourth table will have multiple rows that will match, bu
t
I only need the latest row based on a date field in the row. Here's the par
t
that really is biting me - I need to have, in the select list for display,
two fields from the row the has the latest date, I need yet a different fiel
d
(from the same row) to join to the last table - so in essence, I need (3 plu
s
fields) the whole row which is making it difficult to use a subquery. - I
have been expirimenting with putting select statements in the select list,
select statements in the from clause, select statements in the Having clause
(of a version where I am using a group by) -- query and details below -
Select d.DA_Case_Id,
d.DA_Case_Nbr,
IsNull(S.Initials, 'not assigned') As 'Assigned DA/ADA',
IsNull(bso.name, 'unknown') As 'Last Modified By',
ds.Display_Name As 'Defendant',
bse.string_parm,
bse.raised_on
From DACase d
left join Subject S on d.Assigned_To_Id = S.Subject_Id
left join bs_relationships bs On d.da_case_id = bs.c_globject_id
left join Subject ds on bs.a_globject_id = ds.Subject_Id
join bs_events bse on d.da_case_id = bse.number_parm
join bs_objects bso on bse.subject_globjectid = bso.globject_id
Where Gateway_opt_out_flag <> 'F'
And bse.eventclass_id = 412
And bse.string_parm Like 'Enabled%'
And bs.relationship_type_id = 10
Group By DA_Case_Id, DA_Case_Nbr, S.Initials, ds.Display_Name, bso.name,
bse.string_parm, bse.raised_on
Having bse.raised_on = (Select Max(bse.raised_on))
Order by d.da_case_nbr asc, ds.display_name asc, bse.raised_on desc
The select in the having doesn't seem to do a thing - I am still getting
multiple rows from the bs_events table (the fourth join) - I need to limit
this join to only return one row - but again, I need to display two values
from this row, use another value from the row to join to the fifth table, an
d
the value that is used to get from the third table to the bs_events table is
yet another value (from the same one row) - is there a way this can be
accomplished?
cheers ~a good night sleep, fresh pair of eyes and your comments - thank you! I hav
e
it solved!
Myles
"ML" wrote:

> You know how sometimes you see an impenetrable wall in front of you, and w
hen
> you take two steps back you realize it's just a giant popsicle? :)
> For the fourth join use a derived table, based on a subquery with
> aggregation (MAX) and appropriate grouping. If you post DDL, we can help y
ou
> better.
>
> ML|||I bet the sleep did it. :)
ML|||Hello, Myles
You probably want something like this:
Select d.DA_Case_Id,
d.DA_Case_Nbr,
IsNull(S.Initials, 'not assigned') As 'Assigned DA/ADA',
IsNull(bso.name, 'unknown') As 'Last Modified By',
ds.Display_Name As 'Defendant',
y.string_parm,
y.raised_on
>From DACase d
left join Subject S on d.Assigned_To_Id = S.Subject_Id
left join bs_relationships bs On d.da_case_id = bs.c_globject_id
left join Subject ds on bs.a_globject_id = ds.Subject_Id
join (
select bse1.*
from bs_events bse1
inner join (
select bse2.number_parm,
max(bse2.raised_on) as last_raised_on
from bs_events bse2
group by bse2.number_parm
) x on bse1.number_parm=x.number_parm
and bse1.raised_on=x.last_raised_on
) y on d.da_case_id = y.number_parm
join bs_objects bso on y.subject_globjectid = bso.globject_id
Where Gateway_opt_out_flag <> 'F'
And y.eventclass_id = 412
And y.string_parm Like 'Enabled%'
And bs.relationship_type_id = 10
Order by d.da_case_nbr asc, ds.display_name asc, bse.raised_on desc
The query is untested, because you didn't provide DDL, sample data and
expected results; see: http://www.aspfaq.com/etiquette.asp?id=5006
Razvan