SQL - searching 2 tables and returning results from one

I currently have 2 tables for tickets. Table 1 is tickets and table 2 is repsonses.

I currently do

select id, subject, description 
from tickets 
where subject like '%hello%';

This works but I also want to return the same fields but where hello matches the message field in the responses table.

E.g. I was hoping I could do:

select id, subject, description 
from tickets, responses 
where tickets.subject like '%hello%' OR responses.message like '%hello%'; 

ticketid in the responses table is the same as id in the tickets table.

Answers


If there will always be a responses record:

  select tickets.id, tickets.subject, tickets.description 
  from tickets, responses 
  where
    tickets.id = responses.id and
    ( tickets.subject like '%hello%' or 
      responses.message like '%hello%' )

If tickets can exist without responses:

  select tickets.id, tickets.subject, tickets.description 
  from tickets left outer join responses on tickets.id = responses.id
  where
    tickets.subject like '%hello%' or 
    responses.message like '%hello%'

You probably need left join -

select id, subject, description 
from tickets left join responses on tickets.id = responses.ticketid
where tickets.subject like '%hello%' OR responses.message like '%hello%';

[Correction] LEFT JOIN


You need to join the tables:

SELECT `tickets`.`id`, `tickets`.`subject`, `tickets`.`description`, `responses`.`message` 
LEFT JOIN `responses` ON `tickets`.`id` = `responses`.`id` 
WHERE `tickets`.`subject` like '%hello%' OR responses.message like '%hello%'

If I'm understanding correctly, you only want the results from the tickets table? You could use tableName.* or use the tableName.columnName to get just the results from that original table.

select tickets.id, tickets.subject, tickets.description 
from tickets, responses 
where tickets.id = responses.id
AND (tickets.subject like '%hello%' 
OR responses.message like '%hello%')

Need Your Help

MYSQL query to return all descendants of a specific parent

mysql sql

I have this parent table. my goal is to find out given an id all of its decendents.

Reusing Memory in C++

c++ gcc memory-management

Just wondering is this kind of code recommended to increase performance?