left outer join with <> - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

left outer join with <>

Author

Topic

mohdmartin
Starting Member

22 Posts

Posted - 2007-09-12 : 08:24:16

I am using following query

SELECT e.rec_id,e.EMP_ID,e.FIRST_NAME,e.PROJECT_ID,
e.DEPARTMENT_CODE, e.POSITION_CODE,e.WORKING_STATUS,
d.department_name,p.position_name, c.country_code,
c.country_name
FROM employee e LEFT OUTER JOIN position p
ON e.position_code = p.position_code LEFT OUTER JOIN department d
ON e.department_code = d.department_code left outer join country c
ON e.country_code = c.country_code

where I use
where e.working_status <>'left company'

it retreive few records, it shoud display all records except where
e.working_status <> 'left company'
i.e. left outer join is not working,

we have approx 1000 employee records, 100 employee left the company.
it should show 900 employee records
but is show few records,
how can use where clause in left outer join it show all records
except where e.working_status <> 'left company'

Thanks
Mateen

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-12 : 08:30:21

select count(*)
from employee e
where e.working_status <> 'left company'

select count(*)
from employee e
where e.working_status = 'left company'

does the sum of these add up to 1000?

PS: If anyone is interested in a SQL position in Connecticut with excellent pay please send me a resume to ValterBorges@msn.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 08:33:36

you have NULL value in working_status

DECLARE @TABLE TABLE
(
	id	int identity,
	status	varchar(10)
)

INSERT INTO @TABLE (status)
SELECT	'a'	UNION ALL
SELECT	'a'	UNION ALL
SELECT	'b'	UNION ALL
SELECT	'b'	UNION ALL
SELECT	'b'	UNION ALL
SELECT	NULL

SELECT	COUNT(*) FROM @TABLE WHERE status = 'a'		-- 2
SELECT	COUNT(*) FROM @TABLE WHERE status <> 'a'	-- 3
SELECT	COUNT(*) FROM @TABLE WHERE status IS NULL	-- 1

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 08:45:09

quote:
how can use where clause in left outer join it show all records
except where e.working_status <> 'left company'

where e.working_status <> 'left company'
or    e.working_status is null

KH
[spoiler]Time is always against us[/spoiler]

mohdmartin
Starting Member

22 Posts

Posted - 2007-09-12 : 09:04:57

Thanks for your response.
Yes, we have null values.

when I use where e.working_status is null
it showing more records but we have to display records which employee
are working.

can you explain your query ie. how to use above fields in your query ?

working status fields two type of records
working_status field
--------------------
working
left company
null

Thanks
Mateen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 09:14:19

basically you have 3 possible values in working_status field
if you want to select records where working_status is equal to 'left company' then
it will be working_status = 'left company'
and the rests of the records you will have to use
where working_status <> 'left company'
or working_status is null

NULL is an unknown state. It will return FALSE when compare with it. If you have null value in your field, you have to handle it separately

KH
[spoiler]Time is always against us[/spoiler]

mohdmartin
Starting Member

22 Posts

Posted - 2007-09-12 : 09:41:51

Thanks you very much.

regards
Mateen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources