How to use join to link values from one table to another

I have two tables.

drivers name number email requests id driverassigned ….

I want to get everything from drivers table that may or may not be mentioned in requests.driverassigned. I have tried using join but it returns rows that have a match. Here is what I have so far.

select drivers.email
       , drivers.`number`
       , drivers.name
       , requests.id 
from drivers join requests on drivers.`number` like requests.driverassigned

I am sure there is a common solution but I don’t have enough sql knowledge to fish it out. Any Suggestions?

Answer

use left join

select drivers.email, drivers.`number`, drivers.name, 
       coalesce(requests.id,'none') as request_id 
from drivers left join requests 
on drivers.`number` like '%'+requests.driverassigned+'%'