CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlcount
Satinder singh
Satinder singh

Reputation: 10208

Select query with joins display count result depend on third join table value

Here the SQL FIDDLE and below tabel schema with values.
I want to select id_emp,fname_emp,TotalProject with a condition whose skillsid in (1,2). Query i tried gives wrong output.

create table employee_emp(
id_emp int identity(1,1),fname_emp varchar(20),lname_emp varchar(20))
 insert into employee_emp values('John','Cena');
 insert into employee_emp values('Michel','shawn');
 insert into employee_emp values('Jay','mac');
 insert into employee_emp values('David','jackson');

create table skills_skl(
idemp_skl int,idskill_skl int
)
insert into skills_skl values(1,1);
insert into skills_skl values(1,2);
insert into skills_skl values(1,3);
insert into skills_skl values(1,4);
insert into skills_skl values(2,2);
insert into skills_skl values(2,3);
insert into skills_skl values(3,1);
insert into skills_skl values(3,4);
insert into skills_skl values(4,2);

create table employee_ejp(
id_ejp int identity(1,1),idemp_ejp int ,idproject_ejp int)
insert into employee_ejp values(1,1);
insert into employee_ejp values(1,2);
insert into employee_ejp values(1,3);
insert into employee_ejp values(2,3);
insert into employee_ejp values(2,2);
insert into employee_ejp values(3,1);
insert into employee_ejp values(4,4);

Query I tried

1)

select a.id_emp,a.fname_emp,count(b.id_ejp) TotalP from employee_emp a
join employee_ejp b on a.id_emp=b.idemp_ejp 
group by a.id_emp,a.fname_emp

2) 

select a.id_emp,a.fname_emp,count(b.id_ejp) TotalP from employee_emp a
join employee_ejp b on a.id_emp=b.idemp_ejp join skills_skl c on c.idskill_skl=a.id_emp
where c.idskill_skl in (1,2)
group by a.id_emp,a.fname_emp

Upvotes: 1

Views: 694

Answers (3)

SutharMonil
SutharMonil

Reputation: 88

This should do the trick:

select emp.id_emp, emp.fname_emp, count(proj.idemp_ejp) as project_count  
from employee_emp emp  
join skills_skl sk  
on emp.id_emp=sk.idemp_skl and sk.idskill_skl in (1,2)  
join employee_ejp proj  
on proj.id_ejp=emp.id_emp  
group by emp.fname_emp, emp.id_emp

First making employee table as the base, then using skills as the filter on the join clause thus reducing the data size then and there.
Second, joining project table so that we can count the no. of times the employee has appeared in the table.

Short and sweet.

Upvotes: 0

WKordos
WKordos

Reputation: 2255

select distinct a.id_emp, COUNT(idproject_ejp) over(partition by a.id_emp) 
from #employee_emp a join #skills_skl b 
on a.id_emp = b.idemp_skl
join #employee_ejp c 
on a.id_emp = c.idemp_ejp
where b.idskill_skl in (1,2)
group by a.id_emp,idproject_ejp

Upvotes: 1

dani herrera
dani herrera

Reputation: 51715

Here the easy approach:

;with emps_1_2 as (
select distinct a.id_emp
  from employee_emp a
  join skills_skl c on c.idemp_skl=a.id_emp     --Be carefull here!
  where c.idskill_skl in (1,2)
)  
select emp.id_emp,
       emp.fname_emp,
       coalesce( COUNT(ejp.id_ejp), 0) as TotalProject 
from       employee_emp emp 
inner join emps_1_2 emp12 
        on emp.id_emp = emp12.id_emp
 left join employee_ejp ejp 
        on emp.id_emp=ejp.idemp_ejp
group by emp.id_emp,emp.fname_emp

Results:

| ID_EMP | FNAME_EMP | TOTALPROJECT |
-------------------------------------
|      4 |     David |            1 |
|      3 |       Jay |            1 |
|      1 |      John |            3 |
|      2 |    Michel |            2 |

Upvotes: 1

Related Questions