problem with the right outer join

Question

problem with the right outer join

chandan_kumar_sahu-987370

Old Hand

Points: 351
More actions
March 15, 2011 at 7:08 am

#248198

I have two tables
EmployeeInfo(emp_id int, emp_name varchar(20), emptype_id int) and
EmployeeType(emptype_id int, emptype_desc varchar(100))
EmployeeInfo(emptype_id) references EmployeeType(emptype_id)
1FTE
2Trainee
3Contract
4Marcopolo
5GET
6Expat
7Transfer
8Professional Trainee
9Bridge
Now, I want to find out how many employees are there of each emptype_id.
I wrote the following query:
SELECT EM.emptype_id, count(em.emp_id) as Employee_Count
FROM EmployeeInfo EM RIGHT OUTER JOIN EmployeeType ET ON EM.emptype_id = ET.emptype_id
group by EM.emptype_id
which is giving the following output
emptype_id count(*)
-------------------------
NULL0
1149
28
380
510
But i want the output as follows , please note that for emptype_id 4,6,7,8,9 has no records in EmployeeInfo table.
emptype_id count(*)
-------------------------
NULL0
1149
28
380
40
510
60
70
80
90
thanks for the rply..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Neka SSC Journeyman Points: 92 More actions · Answer 1

You are using a right join which is only counting employees that belong to each type.

I suspect you are trying to list all employee types irrespective of whether there are employees belong to that type, in which case you need to use a left join:

SELECT ET.emptype_id, count(em.emp_id) as Employee_Count

FROM EmployeeInfo EM LEFT OUTER JOIN EmployeeType ET ON EM.emptype_id = ET.emptype_id

group by ET.emptype_id

Try this and let me know if its right as I'm also learning.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

bizzyjunky (3/15/2011)
You are using a right join which is only counting employees that belong to each type.
I suspect you are trying to list all employee types irrespective of whether there are employees belong to that type, in which case you need to use a left join:
SELECT ET.emptype_id, count(em.emp_id) as Employee_Count
FROM EmployeeInfo EM LEFT OUTER JOIN EmployeeType ET ON EM.emptype_id = ET.emptype_id
group by ET.emptype_id
Try this and let me know if its right as I'm also learning.

You probably need to switch the order of tables to get all EmployeeTypes and the count of employeeInfo with that type.

FROM EmployeeType ET LEFT OUTER JOIN EmployeeInfo EM ON EM.emptype_id = ET.emptype_id

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

chandan_kumar_sahu-987370 Old Hand Points: 351 More actions · Answer 3

chandan_kumar_sahu-987370

Old Hand

Points: 351

March 15, 2011 at 10:28 pm

#1299206

no.. left outer join did'nt helped..

chandan_kumar_sahu-987370 Old Hand Points: 351 More actions · Answer 4

chandan_kumar_sahu-987370

Old Hand

Points: 351

March 15, 2011 at 10:30 pm

#1299207

@sean Lange..

changing the order also didnt helped..

chandan_kumar_sahu-987370 Old Hand Points: 351 More actions · Answer 5

thanks for giving a try. I got it..

I had made a small change and now i got the output perfectly..

In the select statement i had actually changed EM.emptype_id to ET.emptype_id

SELECT ET.emptype_id, count(em.emp_id) as Employee_Count

FROM EmployeeInfo EM RIGHT OUTER JOIN EmployeeType ET ON EM.emptype_id = ET.emptype_id

group by EM.emptype_id

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Try this:

declare @employeetype table

( typeid int , emptype varchar(25) )

insert into @employeetype

select 1 ,'FTE'

union all select 2, 'Trainee'

union all select 3, 'Contract'

union all select 4, 'Marcopolo'

union all select 5, 'GET'

union all select 6, 'Expat'

union all select 7, 'Transfer'

union all select 8, 'Professional Trainee'

union all select 9, 'Bridge'

declare @empid table

(

empid int,

emptype int

)

insert into @empid

select number empid , ((abs(checksum((NEWID()))) % 7 ) ) emptype from master..spt_values

where number between 1 and 200

--select * from @empid

select ET.typeid , COUNT (EI.empid) Cnt_EMp

from @employeetype ET

LEFT JOIN @empid EI

on ET.typeid = EI.emptype

group by et.typeid