CODEWITHSUNDEEP
mysqlsqljoin
srp1908
srp1908

Reputation: 55

Sql selecting values from multiple tables where count is exactly 2

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

Upvotes: 1

Views: 220

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

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

Upvotes: 1

Related Questions