Trouble with outer join coupled with search coupled with group by

I am trying to do 2 things with a query.

1) Return the ticket subject from one table and the matching messages from another table. This is a one to many relationship and I want the return to show the subject with all the messages for each subject concatenated.

2) be able to search against the subject as well as all its notes for a search string.

3) some subjects do not have messages so the join needs to be a left join, but if a search matches I ONLY want the records it matches returned not all the other records from the right table.

This is my attempt which fails because it doesn’t concatenate the messages and it returns extraneous records that don’t match the search criteria:

select t.seq, t.`subject`, n.message from tickets t left join tickets_notes n
on t.seq = n.ticket_seq and (t.`subject` like '%search string%' or n.message like '%search string%')
and t.seq=98795 GROUP BY t.seq;

I appreciate any advice you can offer. (Please note: this query is run against millions and millions of records, so efficiency is a big issue.)

Answer

I think you want to search in either the tickets or the notes table and return a match when you find a match in one or the other. You can do this with left join and filtering:

select t.seq, t.`subject`, group_concat(n.message, '|')
from tickets t left join
     tickets_notes n
     on n.ticket_seq = t.seq and
        n.message like '%search string%'
where t.subject like '%search string%' or
      n.ticket_seq is not null
group by t.seq;

I’m not sure why your query is also searching on a particular seq.

EDIT:

If you want all notes for seq where the match is in either place, then do the filtering after aggregation:

select t.seq, t.subject,
       group_concat(n.message, '|')
from tickets t left join
     tickets_notes n
     on n.ticket_seq = t.seq
group by t.seq
having sum(n.message like '%search string%') > 0 or
       t.subject like '%search string%';

Note: These constructs assume that t.seq is a unique/primary key — as does your original query. That is why t.subject is using in the having with no aggregation.