在两个表中搜索并将结果与​​PDO组合

Question

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.

Answer 1

Use JOIN and OR condition:

SELECT jobs.title, jobs.department,
companies.companyname, companies.city
FROM jobs 
JOIN companies ON companies.id = jobs.company_id
WHERE (jobs.title LIKE ? OR companies.companyname LIKE ?)

With UNION:

SELECT jobs.title, jobs.department,
companies.companyname, companies.city
FROM jobs 
JOIN companies ON companies.id = jobs.company_id
WHERE jobs.title LIKE ?

UNION

SELECT jobs.title, jobs.department,
companies.companyname, companies.city
FROM jobs 
JOIN companies ON companies.id = jobs.company_id
WHERE companies.companyname LIKE ?