problem in filtering the records

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

SQL Server 2005 Forums

Transact-SQL (2005)

problem in filtering the records

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2009-01-28 : 07:03:04

Hi,
I have a table of projects with type 'Y' and 'N'.
An employee may belong to more than one project and hence can have status 'y' or 'n' or both.

now i want to fetch those records with status 'N'. if the employee has both 'Y' and 'N' then he must not be displayed.
how to write a condtion for this?

i tried like this:

select emp_name
from employee,project
EXCEPT
selec emp_name
from employee,project
where proj_type = 'Y'

But this gives me those employees belonging to both Y and N.
how to avoid those records and show only type N?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:08:24

[code]
select emp_name
from employee,project
group by emp_name
having sum(case when proj_type = 'Y' then 1 else 0 end)=0
[/code]

mrm23
Posting Yak Master

198 Posts

Posted - 2009-01-28 : 07:25:09

i tried that. but still i am getting more rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 07:31:26

What can I say?
You have no correlation between Employee and Project tables, and thus you get ALL combinations!
It's called a CROSS JOIN or Cartesian Product.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:34:14

yup...thats a point...i missed that you should have a where condition which links related columns of two tables.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 07:41:21

[code]select emp_name,
proj_name
from employee
inner join project ON project.emp_id = employee.emp_id

EXCEPT

select emp_name,
proj_name
from employee
inner join project ON project.emp_id = employee.emp_id
where project.proj_type = 'y'[/code]

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-28 : 07:41:23

Not exact approach to ur problem but try like this

declare @table table(empname varchar(33),projtype char(1))
insert into @table
select 'rahul','y' union all
select 'rahul','n' union all
select 'amir','n' union all
select 'amir','y' union all
select 'swen','n'

select empname from @table where projtype = 'n'
except
select empname from @table where projtype = 'y'

Jai Krishna

mrm23
Posting Yak Master

198 Posts

Posted - 2009-01-28 : 07:44:25

Hi Peso,
I have tried using except. i must get 5 records, i am getting 11.
6 belong to both Y and N.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-28 : 07:45:55

quote:

Originally posted by Peso

select		emp_name,
		proj_name
from		employee
inner join	project ON project.emp_id = employee.emp_id
where		project.proj_type = 'n' 

EXCEPT

select		emp_name,
		proj_name
from		employee
inner join	project ON project.emp_id = employee.emp_id
where		project.proj_type = 'y'

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 07:50:40

[code]select emp_name
from employee
inner join project ON project.emp_id = employee.emp_id
group by emp_name
having max(proj_type) = 'n'[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:48:27

quote:
Originally posted by mrm23

Hi Peso,
I have tried using except. i must get 5 records, i am getting 11.
6 belong to both Y and N.

thats because of cross join. did you add the join condition as suggested?


select e.emp_name
from employee e
join project p
on p.emp_id = e.emp_id
group by emp_name
having sum(case when p.proj_type = 'Y' then 1 else 0 end)=0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources