SQL question

[rmelnyck]

Say I have a table with 2 columns, ID and DEPARTMENT.

ID DEPARTMENT
1 A
1 B
1 C
1 D
2 B
2 D
3 B
3 D
3 E
4 NULL

I need a select that will give me the ID's who do not have a corresponding DEPARTMENT A or C or who have NULL in the DEPARTMENT field.

So in this case the result set will be ID's 2,3 and 4 because ID one contains DEPTS A and C. If the ID has an A or a C I do not want it.

Any ideas? Thanks.

 

[Jamfool]

select ID from table_name
where 
department <> 'A' 
or department <> 'C' 
or department is null
group by ID

 

[vongrunt]

select ID
from blah
group by ID
having sum(case when DEPARTMENT in ('A', 'C') then 1 else 0 end) = 0

Btw. what if one ID has one department in (A, C) and another one NULL?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rmelnyck]

Thanks.

If one ID has one department A or C and another one null I don't want it.

 

[vongrunt]

Then query from above should be OK...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Jamfool]

wrote that wrong:

select distinct id from table_name 
where id not in (
			select ID from table_name
			where 
			(
			department = 'A' 
			or department = 'C'
			)
		)
or department is null

 

[r937]

jamfool, be careful of mixing ORs and NOTs

this --

department <> 'A' or department <> 'C'

will always be true for all rows


r937.com | rudy.ca

 

[Jamfool]

haha, oh yeah. lol ty r937, you can tell ive been banging my head against a brick wall all day