I have two tables. When the user start typing in a searchbox, code select matchig term from two tables and retrieve data from the first one. As an example:
Table A (movies data)
| Movie_code | Movie_Name | Company |
|---|---|---|
| ABx1 | Love in Umbria | Paramount |
| TR55 | Whales and dolphins | Metro |
| HG2 | In the beginning | Paramount |
| YU9 | Afterglow | Columbia |
Table B (actors)
| Movie_code | Actor_Name |
|---|---|
| ABx1 | Tom Hanks |
| ABx1 | Isabelle Huppert |
| ABx1 | Ralph Fiennes |
| ABx1 | Marcello Mastroianni |
| TR55 | Clint Eastwood |
| TR55 | Mia Farrow |
| TR55 | Isabelle Huppert |
I know how to search in one table:
SELECT *
FROM TableA
WHERE Movie_code LIKE ? OR Movie_Name LIKE ? OR Company LIKE ?
and then binding the parameter %SearchTerm%. For "Metro" I would get one result, for "Paramount" I wil get two results, and for "um" there will be two results (the first movie, Love in Umbria, and the last one, for the company name, Columbia).
I have tried a LEFT JOIN for searching in the two tables:
SELECT a.*
FROM TableA a
LEFT JOIN TableB b ON a.Movie_code=b.Movie_code
WHERE a.Movie_code LIKE ? OR a.Movie_Name LIKE ? OR a.Company LIKE ?
OR b.Actor_Name LIKE ?
My problem is that for a single letter (say "a") the code finds many results in Table B (actors), resulting in a repetition of movies. Entering letter "a" should retrieve only the four movies, "Huppert" should return two results, "Afterglow" only one, and so on.
Any ideas?