I have two tables named companies and jobs. They are related - jobs table has a foreign key company_id.
company table columns are: companyname, city, province etc.
jobs table columns: title, department, description etc
I want to search both tables in those columns and if the keyword has a match in jobs table get the related data from the company table, and if the match is in the company table, I want to get all the jobs related to the company.
As far as I know in order to search in both tables I have to use UNION, this works:
$sql= "(SELECT title, department FROM jobs WHERE title LIKE ? )
(SELECT companyname,city FROM companies WHERE companyname LIKE ?)";
SELECT jobs.title, jobs.department, companies.companyname, companies.city FROM jobs WHERE (jobs.title LIKE ? OR companies.companyname LIKE ?)