Guja1501 Guja1501 - 7 months ago 24
SQL Question

Better idea to select unique data from table

I am learning SQL. I want to

select
employee
(
emp_name, emp_lname, project_name
) which has only one
project
(not more or less).

I have 3 table in database:

Tables:

create table employee(
emp_id char (5) primary key,
emp_name nvarchar(15) not null,
emp_lname nvarchar(20)
);

create table project(
pr_id char(5) primary key,
project_name nvarchar(10) not null,
project_budjet int
);

create table employee_project(
emp_id char (5) foreign key references employee(emp_id),
pr_id char(5) foreign key references project(pr_id),
constraint premppk primary key(emp_id, pr_id)
);


I am trying to
select
only unique
emp_id
from
employee_project
.

This code gives me unique
emp_id
from
employee_project


select emp_id, count(pr_id) from employee_project
group by emp_id having count(pr_id) = 1


But I need
emp_id
and
pr_id
to
select
emp_name
,
emp_lname
and
project_name
. I try to
select
pr_id
too using
emp_id
what I have already. Code:

select ep.emp_id, ep.pr_id from employee_project as ep,
(
select emp_id, count(pr_id) from employee_project
group by emp_id having count(pr_id) = 1
) CT
where CT.emp_id = ep.emp_id


Now I have everything to select everything what I need about these
employee
and
project
. Finally code:

select employee.emp_name, employee.emp_lname, project.project_name
from employee, project,
(
select ep.emp_id, ep.pr_id from employee_project as ep,
(
select emp_id, count(pr_id) from employee_project
group by emp_id having count(pr_id) = 1
) CT
where CT.emp_id = ep.emp_id
) CK
where CK.emp_id = employee.emp_id and CK.pr_id = project.pr_id


Is there any way to do this easily.

Thanks for help.

Answer

Since there is only one project you are looking for you can use any aggregate function in the group the get the project too. I used min(pr_id) but you could also use avg() or max() for instance.

After that you can join the tables to get all the other column values.

select e.*, p.*
from 
(
  select emp_id, min(pr_id) as pr_id
  from employee_project
  group by emp_id 
  having count(pr_id) = 1
) e2
join employee e on e.emp_id = e2.emp_id
join project p on p.pr_id = e2.pr_id