How to search for two tables

Question

I have a query that searches for a movie by title, actors, description, etc. and orders results for films that have a search term in the title.

select movie.id, movie.title  
from movie 
where 
title like '%searchTerm%' or
cast like '%searchTerm%' or
director like '%searchTerm%' or 
FullPlot like '%searchTerm%' 
order by (title like '%searchTerm%') desc ## this gives priority to results thatr have the search term in the title

Now I have a new table called "AlsoKnownAs". This table contains information about which films are called in other languages. Example Cidade De Deus (2003) AKA City Of God (2003)

I need to also look for this new table while I search for a movie and sort it as secondary. therefore, films containing searchTerm in the AKA table will appear after films containing searchTerm in the title.

I am not sure how to do this. It seems I need to save the term found in a variable so that I can order it.

any help is appreciated

AlsoKownAs table
'id', 'int(11)', 'NO', 'PRI', NULL, 'auto_increment'
'movieId', 'varchar(10)', 'NO', '', NULL, ''
'aka', 'varchar(305)', 'NO', '', NULL, ''
'country', 'varchar(100)', 'YES', '', NULL, ''

Answer 1

, id , .

, , - aka

select id, title from (
    select t1.id, t1.title,
        sum(aka like '%searchTerm%') aka_count
    from movie t1
    left join AlsoKnownAs t2 on t1.id = t2.movieId
    where title like '%searchTerm%' 
    or cast like '%searchTerm%' 
    or director like '%searchTerm%'  
    or FullPlot like '%searchTerm%' 
    or aka like '%searchTerm%'
    group by t1.id, t1.title
) order by (title like '%searchTerm%') desc, (aka_count > 0) desc

Edit

, aka :

select movie.id, movie.title  
from movie 
left join (
    select distinct movieId from AlsoKnownAs where aka like '%searchTerm%'
) t1 on t1.movieId = movie.id
where 
title like '%searchTerm%' or
cast like '%searchTerm%' or
director like '%searchTerm%' or 
FullPlot like '%searchTerm%' or
t1.movieId IS NOT NULL
order by (title like '%searchTerm%'), (t1.movieId IS NOT NULL) desc

Answer 2

, . aka ( ):

SELECT m.id, m.title, a.aka 
FROM movie m
LEFT JOIN AlsoKnownAs a ON m.id = a.movieId
WHERE 
   m.title like '%searchTerm%' or
   m.cast like '%searchTerm%' or
   m.director like '%searchTerm%' or 
   m.FullPlot like '%searchTerm%' or
   a.aka like '%searchTerm%' 
ORDER BY (m.title like '%searchTerm%') DESC, (a.aka like '%searchTerm%') DESC

: dbms - mssql, , .

Answer 3

- , join ?

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

, :

select movie.id, movie.title, AlsoKownAs.aka
from movie 
left join AlsoKnowAs on movie.id = AlsoKnowAs.movieId
where 
title like '%searchTerm%'
-- .. rest skipped

Answer 4

UNION .

select *
from (
    select 1 as qOrder, m.id, m.title, m.cast, m.director, m.fullplot 
    from movie m
    union
    select 2 as qorder, m.id, a.aka as title, m.cast, m.director, m.fullplot
    from akamovie a
    join movie m on a.movieid = m.id
) alltitles
where title like '%searchTerm%' or
    cast like '%searchTerm%' or
    director like '%searchTerm%' or 
    FullPlot like '%searchTerm%' 
order by qOrder asc, (title like '%searchTerm%') desc;