Problem with multiple records returned in query

[aaron.martone]

This is on a MS SQL 2005 Server on a Windows 2003 Server box.

We are attempting to pull back some unique records on a rather large database, however I am encountering an error in my SQL where I am getting back multiple results when I expect one.

For example.

In this database (a law enforcement one) A unit that belongs to an Incident can have multiple dispositions. My SQL is returning back that same unit and incident for every disposition is has. For example: if the unit has 3 dispositions, rather than getting back 1 record of that single unit, I get back 3, with a unique disposition for each record.

My SQL looks like this :

SELECT
DISTINCT LEI.IncidentNumber,
U.UnitNumber
FROM
LEIncident AS LEI
LEFT JOIN LEIncidentUnit AS LEIU
ON LEIU.IncidentID = LEI.IncidentID
LEFT JOIN Unit AS U
ON LEIUP.UnitID = U.UnitID
LEFT JOIN LEIncidentCFSDisposition AS LEICFSD
ON LEICFSD.IncidentID = LEI.IncidentID
LEFT JOIN ValidationSetEntry AS VSE1
ON LEICFSD.vsDisposition = VSE1.EntryID
WHERE
LEIU.PrimaryUnitFlag = 1 -- Unit is the Primary
AND Month( LEI.CreateDateTime ) = '10'
AND Year( LEI.CreateDateTime ) = '2008'
ORDER BY
LEI.IncidentNumber ASC

(In the above code, we are pulling back records for just 10/2008, and the VALIDATIONSETENTRY table acts as a LOOKUP for fields that are prefaced with 'vs' That is how we can determine the actual "description" of the Disposition, rather that its record ID.

Sorry if this is rather intricate, but I appreciate any help offered. Thank you in advance.

[r937]

For example: if the unit has 3 dispositions, rather than getting back 1 record of that single unit, I get back 3, with a unique disposition for each record.

that's the way SQL is supposed to work

if you want to "collapse" multiple rows into one, one way is with GROUP BY

however, this means you won't be able to show detailed information, but rather, only aggregate information, like for example only a count of the number of incidents, not the incidents themselves

make sense?

[aaron.martone]

That makes sense. I kinda thought that the LEFT JOINS would individually mark a separate record for each one of those.

And what you say is EXACTLY what we're looking for. We want an aggregate ability to just count the right number of rows.

But I know in the few times I've used GROUP BY, I get errors saying that the SELECT does not contain an AGGRAGATE function (or something to that effect)

Would I have a single GROUP BY or multiple? If I use GROUP BY, don't I have to modify my SELECT statement?

[r937]

okay, let's analyze what's happening in your FROM clause first

to start with, i think you're using LEFT OUTER JOINs incorrectly

the FROM clause starts out like this --

FROM LEIncident AS LEI
LEFT JOIN LEIncidentUnit AS LEIU

then you have this WHERE clause --

WHERE
LEIU.PrimaryUnitFlag = 1 -- Unit is the Primary
AND Month( LEI.CreateDateTime ) = '10'
AND Year( LEI.CreateDateTime ) = '2008'

the WHERE condition on the LEI date column means that you are interested only in certain incidents, and that's good

however, the WHERE condition on the LEIU column means that the LEFT OUTER JOIN will never return unmatched LEI rows, because unmatched LEI rows would have NULLs in all the columns from the LEIU table, but then the WHERE condition stipulates that PrimaryUnitFlag has to be non-NULL

so basically i think you really want INNER JOINs, right?

with me so far?

to carry on, would you mind indicating the cardinality (many-to-one, or one-to-many) of each of the joins

[aaron.martone]

Trying to grasp the concepts you're teaching me. I know that with SQL, you have to read it in a different order than just top to bottom (thus you start me out with reading the FROM clause, etc.)

My problem offhand was understanding the difference between a LEFT JOIN and an INNER JOIN (I know it has to do with the left/right tables being joined and whether or not to include the matching rows.

Let's see. One of the problems I noted was WHERE I put my post-WHERE clauses. For example, I noticed that I got a different recordcount returned when I did

LEFT JOIN X
ON X.field = Y.field
AND X.other_field = 'foo'

vs.

LEFT JOIN X
ON X.field = Y.field
WHERE
X.other_field = 'foo'

I find that when I put the conditional clauses after the WHERE, I get better results seemingly.

As far as Many To One or One To Many, here's the best I can explain it.

There a multiple incidents
For 1 incident, it can have MULTIPLE units (but only ONE is marked as Primary, indicated by the LEIU.PrimaryUnitFlag flag - we only want primary units back)

But incident can be given MULTIPLE dispositions. We don't care which disposition, we just don't want to pull back 1 record for each disposition on the incident.

I didn't even give you the entire problem. I have a separate query that is (successfully) pulling back officers from the PERSONNEL table who belong to 'D Platoon'. It pulls back their Personnel.PersonnelID which you'll see is used later on:

What we do, is that for each Person this query pulls back, we run the query you and I are working on. The sequence is:

1 Incident can have X Units (but only 1 is Primary) and that Unit references what can be 1 OR X Personnel in that Unit (because a Unit can be manned by 1 person OR sometimes there can be multiple personnel in a unit)

So we can plugin the Officer's Personnel ID as a stipulation to the records returned. All in all, the tables used (which are, thankfully, self explanatory) are:

LEIncidents (LE is a preface for Law Enforcement) which I call LEI
LEUnits which I call LEIU (Units assigned to the Incident)
Unit which I call U (a lookup of the Unit's info, such as number, etc.)
LEUnitPersonnel which I call LEIP (Personnel belonging to the Unit at the time of the incident)
Personnel which I call P (a cross-reference of the UnitPersonnel's info, such as last/first name, etc.)
LEUnitCFSDisposition which I call LEICFSD (a cross-referencing table which contains a vsDisposition field we run against ValidationSetEntry)
ValidationSetEntry which I call VSE (a lookup table that gives us back the description of the Disposition pulled from the LEUnitCFSDisposition) btw (CFS = Call For Service)

I really hope I gave you what you needed to know here without confusing the both of us. If not, feel free to explain and I will do my best to get you the info. I have a bad habit of just LEFT JOINING a table when I need information from it.

[r937]

let's take things slowly, building up the join as we go

  FROM LEIncident AS LEI
INNER
  JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID 
   AND LEIU.PrimaryUnitFlag = 1 
INNER
  JOIN Unit AS U
    ON U.UnitID = LEIU.UnitID
 WHERE LEI.CreateDateTime >= '2008-10-01'
   AND LEI.CreateDateTime  < '2008-11-01'

notice that we have:
- inner joins
- a join condition in the ON clause
- corrected typo in LEIU alias
- initial filter conditions in the WHERE clause (written in the most efficient way)

now, what do you want to join next? just one step at a time, okay?

you said that incidents can have multiple dispositions, but you don't care what they are, so my question is, do you really need to join to the dispositions, and if so, why?

[aaron.martone]

-- Test Code
SELECT
    LEI.IncidentNumber,
    U.UnitNumber
FROM LEIncident AS LEI
INNER JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
    AND LEIU.PrimaryUnitFlag = 1
INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.UnitID = LEIU.UnitID
INNER JOIN Unit AS U
    ON U.UnitID = LEIUP.UnitID
WHERE
    LEI.CreateDateTime > '2008-10-01'
    AND LEI.CreateDateTime < '2008-10-31'

OK, I see that you referenced LEIUP, but we didn't have the ALIAS yet. So I added that as our next step. We have 1 incident (LEI) linked to the Units (LEIU) and then the Personnel in the unit (LEIUP) so afterwards, our next step needed is to cross-reference the Personnel from the LEIUP.

-- Test Code
SELECT
    LEI.IncidentNumber,
    U.UnitNumber,
    P.LastName,
    P.FirstName
FROM LEIncident AS LEI
INNER JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
    AND LEIU.PrimaryUnitFlag = 1
INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.UnitID = LEIU.UnitID
INNER JOIN Unit AS U
    ON U.UnitID = LEIUP.UnitID
INNER JOIN Personnel AS P
    ON P.PersonnelID = LEIUP.PersonnelID 
WHERE
    LEI.CreateDateTime > '2008-10-01'
    AND LEI.CreateDateTime < '2008-10-31'
    AND P.PersonnelID = -325

We use P.PersonnelID of -325 because it's one of the PersonnelID's pulled from a previous SQL query that belongs to the D Platoon (for which this report is running) Am I right?

[r937]

i'm lost

from the name of the table, i would expect that LEIncidentUnit is the relationship table between incidents and their units

and yet, you're joining LEIncidentUnit to LEIncidentUnitPersonnel and then LEIncidentUnitPersonnel to Unit

why?


also, why did you exclude all of the incidents on the last day of october?

[aaron.martone]

Oh, wait, that should be >= and <= for the dates, my bad.

Ok, here's how I'll try to explain it.

LEIncident (LEI) holds unique Incidents

LEIncidentUnit (LEIU) has Foreign Keys that link:
LEIU.IncidentID to LEI.IncidentID and
LEIU.UnitID which links to U.UnitID

LEIncidentUnitPersonnel (LEIUP) has Foreign Keys that link :
LEIUP.IncidentID = LEI.IncidentID and
LEIUP.UnitID = U.UnitID and
LEIUP.PersonnelID = P.PersonnelID

Personnel (aliased as P) contains fields we use to verify the person (LastName, FirstName) and have the record identifier, PersonnelID

Units (aliased as U) contains fields we use to verify the unit (UnitNumber) So with modifications, we have:

-- Test Code
SELECT
    TOP 100 LEI.IncidentNumber,
    U.UnitNumber,
    P.LastName,
    P.FirstName
FROM LEIncident AS LEI
INNER JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
    AND LEIU.PrimaryUnitFlag = 1
INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.UnitID = LEIU.UnitID
INNER JOIN Unit AS U
    ON U.UnitID = LEIUP.UnitID
INNER JOIN Personnel AS P
    ON P.PersonnelID = LEIUP.PersonnelID 
WHERE
    LEI.CreateDateTime >= '2008-10-01'
    AND LEI.CreateDateTime <= '2008-10-31'
    AND P.PersonnelID = -265 -- This is the individual we're currently checking

We went from LEI > LEIU > LEIUP and then cross referenced the U and P tables.

[r937]

Oh, wait, that should be >= and <= for the dates, my bad.

nope, still wrong

if you are indeed storing datetime values (and not date values), then you will eliminate all incidents on october 31st except for any that just happened to occur at midnight precisely

WHERE
    LEI.CreateDateTime >= '2008-10-01'
    AND LEI.CreateDateTime <= '2008-10-31'
    AND P.PersonnelID = -265 -- This is the individual we're currently checking

We went from LEI > LEIU > LEIUP and then cross referenced the U and P tables.

yeah, i guess this works, i was just expecting to see it as LEI > LEIU > P > LEIUP

the colour coding helps, doesn't it?

as for the last condition in the WHERE clause, this should be moved to the approriate join's ON condition

the WHERE clause should contain only the most limiting filter conditions for the first table in the chain

[aaron.martone]

(Forego all previous colors - I changed them now to introduce a table I had forgotten, LEIncidentCFSDisposition). Also, I temporarily added "TOP 100" to the SELECT to prevent times where 600,000+ records are coming back (don't want to tax our live database like that with faulty SELECTS I'm making) The account I'm connected as only has SELECT rights, so no worry on accidentally screwing up like database.

-- Test Code
SELECT
    TOP 100 LEI.IncidentNumber,
    U.UnitNumber,
    P.LastName,
    P.FirstName,
    P.MiddleName,
    VSE1.Description AS Disposition,
    LEI.CreateDateTime

FROM LEIncident AS LEI

INNER JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
    AND LEIU.UnitID = U.UnitID
    AND LEIU.PrimaryUnitFlag = 1 -- Unit must be primary

INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.PersonnelID = P.PersonnelID
    AND LEIUP.UnitID = U.UnitID
    AND LEIUP.IncidentID = LEI.IncidentID

INNER JOIN LEIncidentCFSDisposition AS LEICFSD
    ON LEICFSD.IncidentID = LEI.IncidentID

INNER JOIN Personnel AS P
    ON P.PersonnelID = LEIUP.PersonnelID 
    AND P.PersonnelID = -265 -- Tested Individual

INNER JOIN Unit AS U
    ON U.UnitID = LEIU.UnitID

INNER JOIN ValidationSetEntry AS VSE
    ON VSE.EntryID = LEICFSD.vsDisposition

WHERE
    LEI.CreateDateTime >= '2008-10-01'
    AND LEI.CreateDateTime < '2008-11-01'

I'm getting back an error:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "U.UnitID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "P.PersonnelID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "U.UnitID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "VSE1.Description" could not be bound.

and don't understand why. It looks like the U, P and VSE were properly joined. I don't know if this color-coding is helping or not -- sure is purdy, though.

You've been very helpful as always Rudy. Thanks.

[r937]

you're going a bit too fast for me, i wanted to do one table at a time

when i last knew what was going on (remember, i'm still trying to understand the one-to-many relationships), we had reached this point --

  FROM LEIncident AS LEI
INNER 
  JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
   AND LEIU.PrimaryUnitFlag = 1
INNER 
  JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.UnitID = LEIU.UnitID
INNER 
  JOIN Unit AS U
    ON U.UnitID = LEIUP.UnitID
INNER 
  JOIN Personnel AS P
    ON P.PersonnelID = LEIUP.PersonnelID 
   AND P.PersonnelID = -265 -- This is the individual 
 WHERE LEI.CreateDateTime >= '2008-10-01'
   AND LEI.CreateDateTime  < '2008-11-01'

i think that the restriction on P.PersonnelID is probably introduced too late

you have LEI > LEIU > LEIUP which retrieves every LEIUP connected to the LEIU

later you return the P for every LEIUP, and only then throw everybody away except the one guy you want

so i would move the condition up higher in the chain of joins --

  FROM LEIncident AS LEI
INNER 
  JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
   AND LEIU.PrimaryUnitFlag = 1
INNER 
  JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.UnitID = LEIU.UnitID
   AND LEIUP.PersonnelID = -265 -- This is the individual 
INNER 
  JOIN Unit AS U
    ON U.UnitID = LEIUP.UnitID
INNER 
  JOIN Personnel AS P
    ON P.PersonnelID = LEIUP.PersonnelID 
 WHERE LEI.CreateDateTime >= '2008-10-01'
   AND LEI.CreateDateTime  < '2008-11-01'

please let me know that this works before we start adding additional tables

[aaron.martone]

Your code works, but hits my TOP 100 cap. I took off the cap and it returned 628,104 rows.

With the 100 cap in place, I noticed that (I returned LEI.IncidentNumber from the SELECT), the IncidentNumber is the exact same for all 100 records returned.

[r937]

and the problem with that is... ?

in other words, maybe it returns that many rows because there are that many rows to return, based on the conditions

the nice thing about writing the FROM clause in a logical sequence is that you can "peel off" the later joins to see where something is going amiss

what do you get for this query

SELECT COUNT(*)
  FROM LEIncident AS LEI
 WHERE LEI.CreateDateTime >= '2008-10-01'
   AND LEI.CreateDateTime  < '2008-11-01'

and now what do you get for this query

SELECT COUNT(*)
  FROM LEIncident AS LEI
INNER 
  JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID
   AND LEIU.PrimaryUnitFlag = 1
 WHERE LEI.CreateDateTime >= '2008-10-01'
   AND LEI.CreateDateTime  < '2008-11-01'

[aaron.martone]

The initial problem is that we are wanting a tally back, but at most, 1 incident can only have 1 primary unit (with 1 OR more personnel) but for us to have 100+ records where the IncidentID is the same, then the tally would be wrong. **edit** To expand on that. They want to know how many incidents said personnel responded to where he was the Primary Unit of that incident. Hopefully this explains why 100+ records of the same incident won't work for us, as we're wanting a distinct tally of the separate incidents.

To the first code you provided, I got back:

(no column name) 13626

And for the second I got back:

(no column name) 12878

[r937]

They want to know how many incidents said personnel responded to where he was the Primary Unit of that incident. Hopefully this explains why 100+ records of the same incident won't work for us, as we're wanting a distinct tally of the separate incidents.

thank you, that is a most descriptive requirements spec

as for the tally, we're getting there, hold on

the first thing in SQL development is joining the tables correctly

i think you will agree that if you get this step wrong, there's not much else you can do in the rest of the query to come up with a correct result (not that your approach was wrong, because it wasn't)



but your spec suggests to me that you want to tally a particular person's incidents, rather than some kind of analysis of last month's incidents in which a particular person was involved

note that our where clause, which i thought would be most restrictive, was on the LEI table, but these specs suggest we should start the FROM clause with P, and a WHERE condition on this person's unique id

i know that this seems like splitting hairs, but when the joins are made from PK to FK, rather than the other way around--and i do realize this may seem obscure when stated with such generality--you often get better performance in addition to clarity of intent

does any of this help?

[r937]

To the first code you provided, I got back:

(no column name) 13626

And for the second I got back:

(no column name) 12878

you had 13626 incidents in october

some of them weren't associated with any primary units

[aaron.martone]

Still stumped as ever, but I tried to put my best foot forward

-- Reverse Order
SELECT
    TOP 200 P.LastName,
    P.FirstName,
    P.PersonnelID,
    LEI.IncidentNumber,
    LEIU.PrimaryUnitFlag,
    LEI.CreateDateTime
FROM Personnel AS P

INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.PersonnelID = P.PersonnelID

LEFT JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentUnitID = LEIUP.UnitID
    --AND LEIU.PrimaryUnitFlag = 1

INNER JOIN LEIncident AS LEI
    ON LEIUP.IncidentID = LEI.IncidentID
    AND LEI.CreateDateTime >= '2008-10-01'
    AND LEI.CreateDateTime  < '2008-11-01'

WHERE
    P.PersonnelID = -265 -- Test Person

The following code returns me back 172 records, making me feel I'm on the right track. But EVERY record returns back NULL for the LEIU.PrimaryUnitFlag, and I KNOW that is wrong, because this individual HAS been a Primary Unit for a couple incidents during this month.

The same results tell me that the CREATEDATETIME and INCIDENTNUMBER are unique, which is a good indicator as far as data goes. Unique dates/time created along with their respective incident numbers - that's a step in the right direction.

However, understandably, once I remove the comment for the PrimaryUnitFlag, I get back 0 records (because as-is, all PrimaryUnitFlag fields are coming back NULL)

Also, the LAST/FIRSTNAME on each record is the same, so I think the clause on the P table is working fine.

I have a bad feeling the people who created this table may have screwed up. There is a LEIncidentUnits table (LEIU) but there is also a Units table (U). In the LEIU, the PK is called IncidentUnitID but there is also a FK called UnitID. In the Unit table, the PK is called UnitID. I would assume by the current naming structure that the LEIU.UnitID FK points to the Unit.UnitID PK field, but if that's the case, why the heck would the LEIncidentUnitPersonnel (LEIUP) table have a FK UnitID as well (cause it does)?? Very confusing why they set this up, but I have a stronger feeling it's just because I don't know their schema, and they did everything right in the end.

[r937]

... but I have a stronger feeling it's just because I don't know their schema, and they did everything right in the end.

me too!!!!!

listen, i'm sorry, but i have to leave this for now, i won't be able to get back to this until tomorrow afternoon

[aaron.martone]

Absolutely not a problem Rudy. I'm VERY appreciative of how helpful you've been.

[r937]

sorry for the delay

i've re-read this thread and the last part of it makes me very uneasy about going any further

one thing i do hope you may have learned is how to do "forensic" querying

start with first table that will have the most restriction (WHERE conditions), and then build up your FROM clause one table at a time, at each step verifying that the correct rows are being joined

later, when all the steps work, and the join is returning the correct data, only then should you start with the GROUP BY

make sense?

[r937]

There is a LEIncidentUnits table (LEIU) but there is also a Units table (U). In the LEIU, the PK is called IncidentUnitID but there is also a FK called UnitID. In the Unit table, the PK is called UnitID. I would assume by the current naming structure that the LEIU.UnitID FK points to the Unit.UnitID PK field, but if that's the case, why the heck would the LEIncidentUnitPersonnel (LEIUP) table have a FK UnitID as well (cause it does)?? Very confusing why they set this up, but I have a stronger feeling it's just because I don't know their schema, and they did everything right in the end.

identifying relationships are transitive

for example if A has many Bs, and each B has many Cs, then C will have a foreign key to A by virtue of its foreign key to B which is the same as B's primary key, which will include the foreign key to A (because it's an identifying relationship)

if that made no sense, don't worry about it

you need to find out what each relationship means

two different FKs to the UnitID might have different roles, e.g. one unit initiated the incident, another unit handled the incident (i'm obviously making this up)

[aaron.martone]

I hear what you're saying. Sadly, the developer of our database in question won't hand over their schema (understandably) but at least they DO help us out when we want to know what field/table houses the data we're looking for and how they get it.

(I've asked them SO MANY TIMES for some kind of tool that would just show us the SQL that's being run on the DB from the frontend tool they built for us, but it's been a no-go)

Here's what I have so far, and I feel it's so far, pulling back the right data.

SELECT
    TOP 200 P.LastName,
    P.FirstName,
    P.PersonnelID,
    LEI.IncidentNumber,
    LEIU.PrimaryUnitFlag,
    LEI.CreateDateTime
FROM Personnel AS P

INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.PersonnelID = P.PersonnelID

LEFT JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentUnitID = LEIUP.UnitID
    AND COALESCE(LEIU.PrimaryUnitFlag,1) = 1

INNER JOIN LEIncident AS LEI
    ON LEIUP.IncidentID = LEI.IncidentID
    AND LEI.CreateDateTime >= '2008-10-01'
    AND LEI.CreateDateTime  < '2008-11-01'

WHERE
    P.PersonnelID = -265 -- Test Person
    AND LEI.vsSource IN (72989, 72997)

ORDER BY IncidentNumber ASC

This is returning me 106 records, but oddly enough all the PrimaryUnitFlags are coming back NULL, even though I thought it explicity would only pull in ones marked by a 1

[r937]

... but oddly enough all the PrimaryUnitFlags are coming back NULL, even though I thought it explicity would only pull in ones marked by a 1

actually, you explicitly requested all the ones which are either NULL or 1

COALESCE(foo,1)=1 will return foo when it is NULL

[aaron.martone]

Ah, OK, that makes sense.

I've taken what you've taught me and got this code, and it REALLY seems to be on the mark. (We're verifying on the front-end side best we can)

SELECT
    TOP 200 LEI.IncidentID,
    LEI.IncidentNumber,
    (P.LastName + ', ' + P.FirstName + ' ' + P.MiddleName) AS OfficerName,
    LEIU.PrimaryUnitFlag,
    VSE1.Description AS Disposition,
    VSE2.EntryValue AS IncidentSource,
    VSE3.EntryValue AS IncidentType, LEI.vsIncidentType
FROM LEIncident AS LEI
INNER JOIN LEIncidentUnit AS LEIU
    ON LEIU.IncidentID = LEI.IncidentID    
    AND LEIU.PrimaryUnitFlag = 1 -- Unit is the primary
INNER JOIN Unit AS U
    ON U.UnitID = LEIU.UnitID
    AND U.UnitID = LEIU.UnitID
INNER JOIN LEIncidentUnitPersonnel AS LEIUP
    ON LEIUP.IncidentID = LEI.IncidentID
    AND LEIUP.UnitID = U.UnitID
INNER JOIN Personnel AS P
    ON P.PersonnelID = LEIUP.PersonnelID
    AND P.PersonnelID = -1361 -- Personnel to test
INNER JOIN LEIncidentUnitDisposition AS LEIUD
    ON LEIUD.IncidentID = LEI.IncidentID
    AND LEIUD.UnitID = U.UnitID
INNER JOIN ValidationSetEntry AS VSE1
    ON LEIUD.vsDisposition = VSE1.EntryID
INNER JOIN ValidationSetEntry AS VSE2
    ON LEI.vsSource = VSE2.EntryID
INNER JOIN ValidationSetEntry AS VSE3
    ON LEI.vsIncidentType = VSE3.EntryID
    AND VSE3.EntryID IN (-20456, -20486, -21157, 72918, -20524) -- Traffic Arrest, Traffic Incident, Follow-Up, Suspicious Incident, Suspicious Person
WHERE
    LEI.CreateDateTime >= '2008-10-01' -- From the beginning time
    AND LEI.CreateDateTime  < '2008-11-01' -- To the ending time
    AND LEI.vsSource IN (72989, 72997) -- Call source is 'officer' or 'officer-initiated'
ORDER BY
    LEI.IncidentNumber ASC

I think this is returning good info now... (checks)