Specific Distinct Results in Search

[Kupo?]

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.

[r937]

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

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 )

[Kupo?]

Thanks for the reply. I had to add some parenthesis in to get rid of an error.

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:

incident  description    summary      incidentnoteid
--------  -------------  -------      --------------
3         bought pencil  need pencil  12
3         sent pencil    need pencil  13
3         got pencil     need pencil  14

Now:

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?

[r937]

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

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 )

[Kupo?]

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 =/).

[r937]

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