Sql selecting values from multiple tables where count is exactly 2

Question

I'm trying to get the first_name and last_name from employees database if the emp_no (employee number) exists exactly 2 times in dept_emp.

The employees database consists of: employees, departments, dept_manager, dept_emp, titles, salaries.

USE employees;
SELECT emps.first_name,emps.last_name
FROM employees emps INNER JOIN
 employees.dept_emp dm
 ON emps.emp_no = dm.emp_no INNER JOIN
 employees.titles t
 ON t.emp_no = emps.emp_no INNER JOIN
 employees.departments d
 ON d.dept_no = dm.dept_no
 HAVING COUNT(dm.emp_no) = 2

Example from dept_emp where the first value is emp_no :

(10045,'d004','1996-11-16','9999-01-01'),
(10046,'d008','1992-06-20','9999-01-01'),
(283344,'d001','1996-05-08','1997-11-25'),
(283344,'d009','1997-11-25','9999-01-01'),

Employees table:

CREATE TABLE employees (
emp_no      INT             NOT NULL,
birth_date  DATE            NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,    
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no)

dept_emp table:

CREATE TABLE dept_emp (
emp_no      INT             NOT NULL,
dept_no     CHAR(4)         NOT NULL,
from_date   DATE            NOT NULL,
to_date     DATE            NOT NULL,
FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,dept_no)

Desired output : first_name and last_name if emp_no occurs exactly twice in dept_emp

"Desired output : first_name and last_name if emp_no occurs exactly twice in dept_emp" you mean two diffent emp_no number or doesn't matter? — Raymond Nijland
– Raymond Nijland, Commented Oct 3, 2018 at 16:49

Madhur Bhaiya · Accepted Answer · 2018-10-03 16:50:28Z

1

You are missing a GROUP BY clause.

SELECT emps.first_name,emps.last_name
FROM employees emps 
INNER JOIN dept_emp dm
  ON emps.emp_no = dm.emp_no 
GROUP BY dm.emp_no, emps.first_name, emps.last_name
  HAVING COUNT(dm.emp_no) = 2

edited Oct 3, 2018 at 16:50

answered Oct 3, 2018 at 16:47

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Nijland Over a year ago

@srp1908 you might need to use HAVING COUNT(DISTINCT dm.emp_no) = 2 if dm.emp_no needs to be unique..

Madhur Bhaiya Over a year ago

@RaymondNijland I think OP is looking for those cases where a particular emp_no is exactly duplicate in the dept_emp table. "if the emp_no (employee number) exists exactly 2 times in dept_emp."

Collectives™ on Stack Overflow

Sql selecting values from multiple tables where count is exactly 2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related