[RESOLVED] Help with Nested Query using Inner Joins

[Spacehamster]

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 -

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?

[szlamany]

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.

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.

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.

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

[Spacehamster]

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.

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!

[szlamany]

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