SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Specific Distinct Results in Search

    Code:
    SELECT Incidents.Incident
         , IncidentNotes.Description
         , IncidentNotes.Summary
         , IncidentNotes.IncidentNoteID
      FROM Incidents 
    INNER 
      JOIN IncidentNotes 
        ON Incidents.Incident = IncidentNotes.Incident 
    WHERE IncidentNotes.Description Like  '*'&search&'*' 
       OR IncidentNotes.Summary Like '*'&search&'*'
    I'm searching the Summary and Description fields in IncidentNotes then returning any matches, which works fine. However, if more than one incident note within the incident has the search term, then it displays multiple results for a single incident (currently). I would like it to display the incident along with the 'newest incident note' (highest IncidentNoteID) associated with that incident if the term is found (even if the newest incident note doesn't contain the search term).

    I tried using DISTINCT to no avail and I'm also unsure of how to display the newest incident note for each incident returned. I'm very surprised I even made it this far but I seem to be picking stuff up a lot faster now (Rudy's site is great http://r937.com/).
    Thanks for any help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    thank you, kupo?, for the very kind words

    join each Incidents row to all its IncidentNotes rows, and keep your search conditions for those IncidentNotes rows (this will qualify which Incedents rows are chosen), but also join to the latest IncidentNotes row, based on a correlated subquery

    Code:
    SELECT I.Incident
         , IN2.Description
         , IN2.Summary
         , IN2.IncidentNoteID
      FROM Incidents as I
    INNER 
      JOIN IncidentNotes as IN1
        ON I.Incident = IN1.Incident 
    INNER 
      JOIN IncidentNotes as IN2
        ON I.Incident = IN2.Incident     
     WHERE ( 
           IN1.Description Like  '*'&search&'*' 
        OR IN1.Summary Like '*'&search&'*'
           )
       and IN2.IncidentNoteID
         = ( select max(IncidentNoteID)
               from IncidentNotes
              where Incident = I.Incident )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. I had to add some parenthesis in to get rid of an error.
    Code:
    SELECT I.Incident
         , IN2.Description
         , IN2.Summary
         , IN2.IncidentNoteID
      FROM (Incidents as I
    INNER 
      JOIN IncidentNotes as IN1
        ON I.Incident = IN1.Incident) 
    INNER 
      JOIN IncidentNotes as IN2
        ON I.Incident = IN2.Incident     
     WHERE ( 
           IN1.Description Like  '*'&search&'*' 
        OR IN1.Summary Like '*'&search&'*'
           )
       and IN2.IncidentNoteID
         = ( select max(IncidentNoteID)
               from IncidentNotes
              where Incident = I.Incident )
    It's returning exactly what I want it to now, except it's still listing duplicates. It's not because I added the parenthesis wrong is it?

    Before:
    Code:
    incident  description    summary      incidentnoteid
    --------  -------------  -------      --------------
    3         bought pencil  need pencil  12
    3         sent pencil    need pencil  13
    3         got pencil     need pencil  14
    Now:
    Code:
    incident  description  summary      incidentnoteid
    --------  -----------  -----------  --------------
    3         got pencil   need pencil  14
    3         got pencil   need pencil  14
    3         got pencil   need pencil  14
    Is this easily remedied?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    my bad, i forgot about the dupes, and of course i should've seen that it was Access and needed the parentheses

    yes, you can now add DISTINCT after SELECT in the above query, or else change it to this one --
    Code:
    SELECT I.Incident
         , IN2.Description
         , IN2.Summary
         , IN2.IncidentNoteID
      FROM Incidents as I
    INNER 
      JOIN IncidentNotes as IN2
        ON I.Incident = IN2.Incident     
     WHERE exists
           ( 
           select 1
             from IncidentNotes
            where Incident = I.Incident
              and (
                  Description Like  '*'&search&'*' 
               OR Summary Like '*'&search&'*'
                  )
           )
       and IN2.IncidentNoteID
         = ( select max(IncidentNoteID)
               from IncidentNotes
              where Incident = I.Incident )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, it's an Access database. Just been reading about how it not a very good database to be using beyond personal use.

    The query works great, thanks again! I tried using distinct, but I think the one you posted actually works faster (there's 208,000 notes in 38,000 incidents it has to search =/).

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the reason i like EXISTS is because the database engine can stop searching as soon as it finds the first row in the subquery

    whereas with DISTINCT it's actually going to return rows, sort them, and then remove duplicates by comparing rows in pairs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •