problem with the right outer join

Question

Post reply

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)

1 FTE
2 Trainee 3 Contract 4 Marcopolo 5 GET 6 Expat 7 Transfer 8 Professional Trainee 9 Bridge

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(*) ------------------------- NULL 0 1 149 2 8 3 80 5 10

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(*) ------------------------- NULL 0 1 149 2 8 3 80 4 0 5 10 6 0 7 0 8 0 9 0

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: 286562 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: 39971 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