How to join 2 tables based on a like in mysql

Question

I have 2 tables Employee and Company_Employee.

Employee: ID, FirstName

Company_Employee: ID, Company_ID, Employee_ID

I want to do a search by First Name. I was thinking my query would look like:

   select FirstName, ID from Employee where FirstName LIKE '%John%' and ID in (select id from Company_Employee)

This query returns no rows. Does anyone know how I can get the rows with a like by FirstName with these 2 tables?

Thanks!

Answer 1

Your query compares a company_employee.id with an employee.id. It should probably compare employee.id with company_employee.employee_id.

You can rewrite the query more clearly with a join:

select *
from employee e
join company_employee ce
    on e.id = ce.Employee_ID
where e.FirstName like '%John%'

Answer 2

Something like this

SELECT
*
FROM
Employee e
INNER JOIN Company_Employee ce JOIN ON e.Id = ce.Id)
WHERE 
FirstName LIKE '%JOHN%'