SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Problem with multiple records returned in query

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let's take things slowly, building up the join as we go
    Code:
      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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    -- 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.

    Code:
    -- 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?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    -- 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.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    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


    Quote Originally Posted by aaron.martone View Post
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    (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.

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

    Code:
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
      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 --
    Code:
      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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    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

    Last edited by r937; Dec 1, 2008 at 16:58. Reason: Edit: primary units
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Still stumped as ever, but I tried to put my best foot forward

    Code:
    -- 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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Absolutely not a problem Rudy. I'm VERY appreciative of how helpful you've been.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

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

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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)

    Code:
    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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •