quote:

---

If i have a table like this:
col1 col2
foo 1
foo 2
foo 3
bar 2
bar 3

How do a get all the distinct values in col1 for rows that are never equal to 1 in col2. In this case the query should return bar only because no row exists with bar and 1 together .

---

Declare @t table (col1 varchar(10), col2 int);
Insert Into @t Values
	('foo', 1),
	('foo', 2),
	('foo', 3),
	('bar', 2),
	('bar', 3);

Select	col1 From (
	Select	 p1.col1, (
		Select	','+Cast(col2 as varchar(10))+','
		From	@t As p2
		Where	p1.col1 = p2.col1
		For	XML PATH('')	) col2
	From	 @t As p1
	Group By col1	) n
Where	col2 Not Like '%,1,%'

/*--this will give me all the distinct values with col2=1
select col1 
from @t
where col2=1
group by col1 
*/

/* now just remove somehow those values (with col2=1)*/
select col1
from @t as t1
where not exists (select col1 from @t as t2 where col2=1 and t1.col1=t2.col1 group by col1)
group by col1


-- or use of except

select col1
from @t as t1
group by col1
EXCEPT
select col1 
from @t
where col2=1
group by col1 

SELECT		Col1
FROM		@t
GROUP BY	Col1
HAVING		MAX(CASE WHEN Col2 = 1 THEN 1 ELSE 0 END) = 0;

SELECT		Col1
FROM		@t
GROUP BY	Col1
HAVING		MIN(Col2) > 1;