[RESOLVED] Help with Nested Query using Inner Joins

NOTE: This is actually for a Crystal Report, but it's the SQL Query that I'm having trouble with. Hopefully you guys (and girls) can help. Also, the table wasn't designed by me, otherwise I would've used proper primary and foreign keys.

I have a Crystal Report that needs to generate a list of stations ('location' column) that have held warrants in excess of 90 days. The report can only look at the latest movement (ie. 'received_date') for a warrant.

I have a Traffic Warrant system with two tables - Warrant and Warrant_Movements. Warrant stores the Warrant details (eg. ID etc...).

If the person being served a warrant changes location, the new location is saved in the Warrant_Movement table. Thus a single Warrant can have multiple entries in the Warrant_Movement table. These are the relevant fields:

Warrant
-----------
ID

Warrant _Movement
-------------------------
ID (same as Warrant.ID)
Received_Date (date that location changes)
Location (the new location the Warrant has moved to).

Here is the SQL Query that retrieves the latest received_date for each warrant -

Code:

---

select
        warrants.id,
        MAX(warrants_movements.received_date) As Max_Received_Date
from
        warrants INNER JOIN
        warrants_movements ON dbo.ib_warrants.id = warrants_movements.id
WHERE
        warrants_movements.received_date Not Like '' AND
        warrants_movements.received_date IS NOT NULL AND
        warrants.disposal_method IS NULL AND
        warrants_movements.received_date < {ts '2007-01-01 00:00:00.00'}
Group by
        warrants.id

---

When I try to add the warrant_movements.location column to the SQL Query, I get this error -

Server: Msg 8120, Level 16, State 1, Line 1
Column 'dbo.ib_warrants_movements.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The Crystal Report needs to be grouped by location, so how can I add the location for each warrant to the query?

Basically - GROUP BY is great for aggregation. But in the long run you want a row from a table that is related is the "last" in a sequence - GROUP BY just doesn't serve that purpose.

If you could create a VIEW of the WARRANT and MAX(RECEIVED_DATE) it would make things a lot easier - you could use that VIEW by JOINING to it.

Code:

---

Create View Warrants_Movements_Max
    As
    Select Id, Max(Received_Date) as Max_Received_Date From Warrants_Movements
    Group by Id

---

This VIEW basically points to the the Id and Received_Date of the row you desire - the "last" in the sequence.

You could then query like this to get your final results.

Code:

---

Select Warrants.Id
    , Warrants_Movements_Max.Max_Received_Date
    , Warrants_Movements.Location
    From Warrants
    Left Join Warrants_Movements_Max on Warrants_Movements_Max.Id=Warrants.Id
    Left Join Warrants_Movements on Warrants_Movements.Id=Warrants_Movements_Max.Id
      and Warrants_Movements.Received_Date=Warrants_Movements_Max.Max_Received_Date

---

If you cannot do this then you need to use several sub-queries to arrive at the same thing in your query.

Code:

---

Select Warrants.Id
    , (Select Max(Received_Date) From ...)
    , (Select Location From... Where Received_Date=(Select Max(Received_Date) From...)
    From Warrants

---

Basically the first sub-query is the "view" logic I proposed.

The second sub-query is a select for the location related to the "first sub-query" - so it's a sub-query with a sub-query.

The VIEW would be much cleaner to look at - but probably execute in the same fashion as the ugly sub-query version

Quote:

---

Originally Posted by szlamany

If you could create a VIEW of the WARRANT and MAX(RECEIVED_DATE) it would make things a lot easier - you could use that VIEW by JOINING to it.

---

The view worked perfectly. I was so focused on the use of subqueries, I didn't even realise that a view would've been a more elegant solution for this case.

Quote:

---

Originally Posted by szlamany

The VIEW would be much cleaner to look at - but probably execute in the same fashion as the ugly sub-query version

---

I prefer the view - in addition to being more understandable, it's also more maintainable as well.

That seems to have worked perfectly. Thanks for all your help!

I'm really glad to hear that it worked for you!