Complex select statment using Mysql

Question

I dont't now how to explain but i have two table in my database.

• tab 1: service
• tab 2: department

service table:

idService
serviceName
department_ID
is_deleted

department table:

departmentId
departmenetName
is_deleted

I want select all services with the departement name but with departement also if there is no service assigned to it. if there is no service assigned to a departement then the flelds will be shown but i must have null or equivalent in idService field. The result would be like this :

serviceID	serviceName	departmentId	departmenetName
1	IT	2	SI
2	Maintenance	6	Mechanical
3	Maintenance	6	Mechanical
4	Opt Manager	7	Finance
5	Instrument	5	Electric
NULL	NULL	1	Civil
8	Agro	NULL	NULL
_________	___________	____________	_______________

I have tried all kind of join options but I couldn't find the logic behind. I use Mysql as DB Any idea please ? Thank you.

Answer 1

you could try using an union between the left joined tables whit and without service

    select a.serviceID, a.serviceNam, b.departmentId, b.departmenetName
    from service a
    left join department b on a.department_ID = b.departmentId 
    UNION 
    select null, null, b.departmentId, b.departmenetName
    from department b 
    left join service a a.department_ID = b.departmentId  
    where a.serviceID is null