Join only one row from child table

Transact-SQL
1

Hi, I have an inner join involving the tables 'arrest' and 'arr_log'. For each 'arrestno' in the arrest table there are many occurrences of it in the arr_log table. It returns a record for each match, but I only want it to return one row and I do not care which one.

SELECT a.arrestno,a.courtcase,a.other,a.dob,b.user_id,c.name,c.unit
FROM Arrest a inner join
arr_log b on a.arrestno = b.key1
inner join empmast c on b.user_key = c.PERNO
WHERE a.ARRESTTYPE = 'W' AND
a.servebyd BETWEEN @StartDate AND dateadd(day,1,@EndDate)
ORDER BY b.event_date desc

I would greatly appreciate your help. Thanks so much.

2

But ...

... you have ORDER BY b.event_date which suggests that you maybe do need to care about which one?

Also the JOIN to empmast is made on b.user_key, so unless all rows from arr_log return the same value for b.user_key that will change the output of the report.

At the very least I would put a REPEATABLE Order By on the selection from arr_log (i.e. Sort on a column set guaranteed to be unique), so that you get the same outcome when the report is re-run

Haven't tested it, but this might be one way:

SELECT a.arrestno,a.courtcase,a.other,a.dob,b.user_id,c.name,c.unit
FROM Arrest a 
CROSS APPLY
(
    SELECT TOP 1 b.user_id, b.event_date, b.user_key
    FROM arr_log AS b
    WHERE b.key1 = a.arrestno
-- OPTIONAL: ORDER BY b.event_date desc, b.user_id, b.user_key
) AS b
inner join empmast c on b.user_key = c.PERNO
WHERE a.ARRESTTYPE = 'W' AND
a.servebyd BETWEEN @StartDate AND dateadd(day,1,@EndDate)
ORDER BY b.event_date desc

Are you sure the endpoint of your BETWEEN is safe? The value of dateadd(day,1,@EndDate) will be included in the selection. To work around that it is more common to do:

AND a.servebyd >= @StartDate 
AND a.servebyd <  @EndDate_PlusOneDay_RoundedToMidnight
3

I suggest using APPLY instead of JOIN, as below.

I figured you might want to insure the date on the arr_log record is within range, so I added (commented) code to do that in case you decide you need to; if so, naturally just uncomment. I also added (commented) code to get the last matching log row if you decide you need to do that.

SELECT a.arrestno,a.courtcase,a.other,a.dob,b.user_id,c.name,c.unit
FROM Arrest a OUTER APPLY (
SELECT TOP (1) * FROM arr_log b2 WHERE b2.key1 = a.arrestno 
    /*AND b2.event_date >= @StartDate AND b2.event_date < DATEADD(DAY,1,@EndDate)*/ 
    /*ORDER BY b2.event_date DESC*/ ) b
inner join empmast c on b.user_key = c.PERNO
WHERE a.ARRESTTYPE = 'W' AND
a.servebyd >= @StartDate AND a.servebyd < dateadd(day,1,@EndDate)
ORDER BY b.event_date DESC