Help SQL - 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

Help SQL

Author

Topic

anchoredwisdom
Starting Member

22 Posts

Posted - 2012-07-18 : 06:02:31

col1 col2 col3 col4
a b c 1

a b c 99

d e f 4

d e f 5

I need the rows where col4= 99 and also rows where col4 <>99
but if a row have col4 =99 all the other rows with same col1,col2,col3 should not return.

In the example row2 should come as col4=99 but row1 does not qualify as col1,col2,col3 are same in a row with col4=99 row3 and row 4 qualifies.

I did this with UNION .
select * from table where col4=99
UNION
select * from table where (col1,col2,col3) not in (select col1,col2,col3 from table where col4 <>99)

Can this be done using not exists or exists?
Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-18 : 08:03:52

There's several ways to do this, here's 2


declare @TABLE TABLE(col1 char(1),col2 char(1),col3 char(1),col4 tinyint)

INSERT @TABLE
VALUES
('a','b','c', 1),

('a','b','c', 99),

('d','e','f', 4),

('d','e','f', 5)


 
select t.col1,t.col2,t.col3,t.col4
 from @TABLE t
 
 where exists(
 select * 
 from @TABLE t1 
 where t.col1 =t1.col1
 and t.col2=t1.col2
 and t.col3 = t1.col3
 
 and t1.col4 = 99
 
 )
 
 

 
 select t1.*
 from @TABLE t1
 inner join
 (
 
 select t.col1,t.col2,t.col3,t.col4
 from @TABLE t
 where t.col4 = 99	
 ) t on
 
  t.col1 =t1.col1
 and t.col2=t1.col2
 and t.col3 = t1.col3

You'll have to experiment to see which one performs better for you.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 09:54:36

quote:

Originally posted by jimf

There's several ways to do this, here's 2


declare @TABLE TABLE(col1 char(1),col2 char(1),col3 char(1),col4 tinyint)

INSERT @TABLE
VALUES
('a','b','c', 1),

('a','b','c', 99),

('d','e','f', 4),

('d','e','f', 5)


 
select t.col1,t.col2,t.col3,t.col4
 from @TABLE t
 
 where exists(
 select * 
 from @TABLE t1 
 where t.col1 =t1.col1
 and t.col2=t1.col2
 and t.col3 = t1.col3
 
 and t1.col4 = 99
 
 )
 
 

 
 select t1.*
 from @TABLE t1
 inner join
 (
 
 select t.col1,t.col2,t.col3,t.col4
 from @TABLE t
 where t.col4 = 99	
 ) t on
 
  t.col1 =t1.col1
 and t.col2=t1.col2
 and t.col3 = t1.col3

You'll have to experiment to see which one performs better for you.

Jim

Everyday I learn something that somebody else already knew

Sorry I dont think this is what OP is asking for.
this returns every row for which a row with col4=99 exists for same col1,col2,col3 values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 10:03:20

i think whats he's looking for is this



method 1
---------------------

SELECT t.*
FROM table t
INNER JOIN (SELECT Col1,Col2,Col3,SUM(CASE WHEN Col4=99 THEN 1 ELSE 0 END) AS Cnt
           FROM table
           GROUP BY Col1,Col2,Col3
           )t1
ON t1.Col1 = t.Col1
AND t1.Col2 = t.Col2
AND t1.Col3 = t.Col3
WHERE t1.Cnt =0 
OR t.Col4 = 99

method 2
----------------

SELECT *
FROM
(SELECT SUM(CASE WHEN Col4 = 99 THEN 1 ELSE 0 END) OVER (PARTITION BY Col1,Col2,Col3) AS Occ,*
FROM table
)t
WHERE Occ = 0
OR Col4 = 99

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anchoredwisdom
Starting Member

22 Posts

Posted - 2012-07-19 : 03:58:19

Thankyou somuch Visakh.
You are a Genious!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 09:44:50

np
you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources