How to check if values from the table 1 are used in table 2?

Question

I have two tables employees and departments.

Departments:

create table dept (
id INT NOT NULL AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL, 
PRIMARY KEY (id)
);

Employees:

create table department_master (
id INT NOT NULL AUTO_INCREMENT,
dept_id INT NOT NULL, 
emp_name VARCHAR(100) NOT NULL, 
PRIMARY KEY (id)
);

I want to prevent departments being deleted from the UI if they are assigned to one of the employees in employee table. Left join is giving me duplicate values.

How do I see if the departments are being used in the employees table.

@scaisEdge I tried join but I did not get the desired result. So I want to know if there is a better way to do this? — Krish, Mar 05 '17 at 17:19
Possible duplicate of [Laravel checking if record exists](http://stackoverflow.com/questions/27095090/laravel-checking-if-record-exists) — Shadow, Mar 05 '17 at 17:21
if you need only a simple check you can use a select where .. if this is clear .. ok otherwise let me know — ScaisEdge, Mar 05 '17 at 17:21

Gurwinder Singh · Accepted Answer · 2017-03-05T17:22:18.730

If you want to prevent a department from being deleted, you can simply add a foreign constraint to the table department_master for dept_id column.

create table department_master (
    id INT NOT NULL AUTO_INCREMENT,
    dept_id INT NOT NULL, 
    emp_name VARCHAR(100) NOT NULL, 
    PRIMARY KEY (id),
    constraint con_dm foreign key dept_id references dept( id )
);

It's default behavior is ON DELETE RESTRICT which means that if there is atleast one row present in the department_master for a given dept_id, it can't be deleted from dept table.

If you want to fetch, the department that don't have any employee record, you can use NOT EXISTS:

select *
from dept d
where not exists (
        select 1
        from department_master m
        where d.id = m.dept_id
        );

@Krish please note the storage engine should be InnoDB for this to work — Raymond Nijland, Mar 05 '17 at 17:22

score 0 · Answer 2 · answered Mar 05 '17 at 17:24

0

I believe you want a count of the number of employees grouped by the department, like so:

SELECT count(*) as employees, d.dept_name  FROM dept AS d LEFT JOIN department_master AS e ON e.dept_id = d.id group by e.dept_id

answered Mar 05 '17 at 17:24

Freid001

2,580
3
29
60

How to check if values from the table 1 are used in table 2?

2 Answers2