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).  The
problem is that the fourth table will have multiple rows that will match, but
I only need the latest row based on a date field in the row.  Here's the part
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 field
(from the same row) to join to the last table - so in essence, I need (3 plus
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, and
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 ~

You know how sometimes you see an impenetrable wall in front of you, and when
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 you
better.


ML

a good night sleep, fresh pair of eyes and your comments - thank you!  I have
it solved!

Myles

Show quoteHide quote

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
    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

Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?
SQL Count Problem