sql - Select Query Joining 3 Tables -

i've 3 tables

personel : id, name
department : id, name
match_dept_per : dept_id, pers_id, workinfo

foreign keys :
dept_id --> department.id
pers_id --> personel.id

example data :

personel :
1, emir civas
2, sercan tuncay

department :
1, sales
2, planning

match_dept_per :
1,1,manager

what i'm trying is, listing peoples names, department names , workinfos like:

id | pers. name | dept name | work info --------------------------------------- 1  | emir civas | sales     | manager 

i can simple select query:

select p.id, p.name, d.name, m.workinfo   personel p, department d, match_dept_per m  p.id = m.pers_id , d.id = m.dept_id; 

here sample fiddle of schema , query.

however need display other persons id's not inserted match_dept_per table. , set "unknown" null values. like:

id | pers. name    | dept name | work info ------------------------------------------ 1  | emir civas    | sales     | manager 2  | sercan tuncay | unknown   | unknown 

since i'm using match_dept_per table, if personel id isn't added, can't anything.

any suggestions ?

use left outer join include persons if not associated other tables:

select p.id,        p.name,        ifnull(d.name, 'unknown') depname,        ifnull(m.workinfo, 'unknown') workinfo   personel p        left outer join match_dept_per m            on p.id = m.pers_id        left outer join department d            on d.id = m.dept_id 

here demo fiddle.

as seem use ms sql, might need use isnull() instead of ifnull(). ommit anyway because think it's better have null in code use data (java, c#, whatever). can control output there.