RomanGP RomanGP - 5 months ago 13
PHP Question

Search in two tables and combine results with PDO

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 ? )
UNION
(SELECT companyname,city FROM companies WHERE companyname LIKE ?)";


I dont know how to get related data from the other table depending on the match. Is it possible with just one call using JOIN?

Thanks.

mnv mnv
Answer

Use JOIN and OR condition:

SELECT jobs.title, jobs.department,
companies.companyname, companies.city
FROM jobs 
WHERE (jobs.title LIKE ? OR companies.companyname LIKE ?)
Comments