Search in two tables

Question

I have a media table with the fields id_midia, nome, tipo and another table of synonyms that has the fields id_midia, sinônimo.

I need to make a query where it checks the two tables if the term typed is contained in the media names or in the synonym.

I’m doing it this way:

$sql = "
  SELECT ayzac_midia_name,ayzac_midia_id 
  FROM `ayzac_midia` 
  WHERE `ayzac_midia`.`ayzac_midia_name` 
  LIKE '%".$searchTerm."%' 
  UNION SELECT ayzac_tag,ayzac_midia_id 
    FROM `ayzac_tags` 
    WHERE `ayzac_tags`.`ayzac_tag` 
    LIKE '%".$searchTerm."%'
";

Only that I need the information from the media table, and when the term found is a synonym it brings the sinônimo instead of nome. And also I need the type in the media table and this way it only returns me the nome and the id. Also, if the term sought exists in the two tables with the same id it displays both tables and this cannot happen.

If anyone can help me with that I would be very grateful.

@Edit Explaining the context:

What I’m doing is a search of movies and series registered in my database, it turns out that the user does not necessarily need to search for the full name of the media he can search for a synonym or a tag, for example Game of Thrones can be searched for by Got, but even if he uses the term Got, I want him to return Game of Thrones.

PS: Got is in a table only for synonyms that contains only id_midia, which is a FK of the media table and the synonym.

Answer 1

I believe you need to make one JOIN with the table ayzac_midia in the second SELECT and this way you can return the name of midia.

Something like this:

$sql = "SELECT ayzac_midia_name,ayzac_midia_id 
  FROM `ayzac_midia` 
  WHERE `ayzac_midia`.`ayzac_midia_name` LIKE '%".$searchTerm."%'    
  UNION 
    SELECT ayzac_tag,ayzac_midia_id, ayzac_midia.ayzac_midia_name 
    FROM `ayzac_tags`
    INNER JOIN `ayzac_midia` ON `ayzac_midia`.`ayzac_midia_id` = `ayzac_tags`.`ayzac_midia_id`
    WHERE `ayzac_tags`.`ayzac_tag` LIKE '%".$searchTerm."%'";

Answer 2

I believe you only want 1 result according to the term sought.

Follows:

$sql = "SELECT DISTINCT
  ayzac_midia_name,
  a.ayzac_midia_id 
FROM
  ayzac_midia a
JOIN
  ayzac_tags b on a.ayzac_midia_id = b.ayzac_midia_id
WHERE
  ayzac_midia_name LIKE '%".$searchTerm."%'
  OR ayzac_tag LIKE '%".$searchTerm."%'";

Sqlfiddle