Im an idiot, simple q, some1 look

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

Im an idiot, simple q, some1 look

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-12-20 : 11:07:52

I have a table which im joining that looks like this
123,1
123,2
123,3
123,4
456,1
456,18
456,71
456,99

I want to return the first column #s where there isnt a 2 in the second column. and i need it as a group by...

like, select column1 from table1 where column2 <> 2....
but that above query would still grab the 123.. because of the fk..
i would like the query to only pull the 456 b/c none of the fks with 456 have a 2... understand?

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2007-12-20 : 11:12:35

select * from table
where column 1 = 456 and column 2 <> 2

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-12-20 : 11:14:13

there are more rows in the table so i cant force in the 456

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-20 : 11:17:10

Here is one way:


create table #t (c1 int, c2 int)
insert #t
select 123,1 union all
select 123,2 union all
select 123,3 union all
select 123,4 union all
select 456,1 union all
select 456,18 union all
select 456,71 union all
select 456,99

select	t.c1
from	#t t
left join (
	select c1
	from #t
	where c2 = 2
	) x
		on x.c1 = t.c1
where	x.c1 is null
group by t.c1

output:
c1          
----------- 
456

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 11:20:50

SELECT DISTINCT Col1
FROM Table t1
LEFT OUTER JOIN
(SELECT DISTINCT Col1
FROM TABLE
WHERE Col2=2) t2
ON t1.Col1=t2.Col1
WHERE t2.Col1 IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 11:39:40

Here is a real fast way to get teh records you want.
Only 1 table scan also.

DECLARE	@Sample TABLE (Col1 INT, Col2 INT)

INSERT	@Sample
SELECT	123, 1 UNION ALL
SELECT	123, 2 UNION ALL
SELECT	123, 3 UNION ALL
SELECT	123, 4 UNION ALL
SELECT	456, 1 UNION ALL
SELECT	456, 18 UNION ALL
SELECT	456, 71 UNION ALL
SELECT	456, 99

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

Stats: The two other suggestions needs 27 reads. My suggestion 3 reads.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 01:36:37

Good one Peso. Other method is


SELECT		Col1
FROM		@Sample
GROUP BY	Col1
HAVING		COUNT(CASE WHEN Col2 = 2 THEN 1 END) = 0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources