must have both condition - 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

must have both condition

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-06 : 23:56:28

how can i select the condition where the one ID must include both blue color and any other red color.

originally data as below:
ID color
1 blue
1 yellow
1 red A
2 blue
2 red B
2 red K
2 red O
2 pink

i tried select id,color where color ='blue' and color like 'red%'

but no result appear.

if i apply OR it will only appear either one.

select id,color where color ='blue' OR color like 'red%'

the condition must have blue and any red color per ID.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-07 : 00:13:39

Select id,color From TableName
Where color ='blue' OR color like 'red%'

veeranjaneyulu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-07 : 00:20:22

This only will give the result either red or blue?

will it appear both?

Where color ='blue' OR color like 'red%'

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-07 : 00:23:24

This Will give Both Red And Blue

veeranjaneyulu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-07 : 00:42:26

seems like only pull either one..some without red also will appear..

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-07 : 04:27:40

original data:

ID color
1 blue
1 yellow
1 red A
2 blue
2 red B
2 red K
2 red O
2 pink
3 blue
3 black
3 purple

i tried running the OR condition i still get either one:

ID color
1 blue
1 red A
2 blue
2 red B
2 red K
2 red O
3 blue

wanted to get if meet both condition:

ID color
1 blue
1 red A
2 blue
2 red B
2 red K
2 red O

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-07 : 04:49:44

Select id,color From YourTableName Where ID in(1,2) and color ='blue' OR color like '%red%'

veeranjaneyulu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-07 : 04:54:00

basically there're lots of ID.

I cant add into the where condition for all ID.

any other way?

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-07 : 05:44:30

Select id,color From TableName where ID <= 2 and color ='blue' OR color like '%red%'

veeranjaneyulu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 05:47:42

quote:
Originally posted by peace

basically there're lots of ID.

I cant add into the where condition for all ID.

any other way?


SELECT t.*
FROM Table t
WHERE EXISTS(SELECT 1
             FROM Table
             WHERE ID = t.ID
             GROUP BY ID
             HAVING COUNT(DISTINCT CASE WHEN color='blue' THEN 1 WHEN PATINDEX('red%',color) > 0 THEN 2 ELSE NULL END) >1
 )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-07 : 22:12:36

ID Agent Color LogDate
ID999 Lorren Red A 8/31/13 16:46
ID999 Mary Blue 9/3/13 12:49
ID999 Mary Blue 9/3/13 12:49
ID999 Mary Blue 9/9/13 14:57
ID999 Mary Blue 9/9/13 14:57
ID999 Sarah Red 9/9/13 15:23
ID999 John Red 9/10/13 1:21
ID999 John Red 9/10/13 1:21
ID999 Mary Blue 9/11/13 7:12
ID999 Mary Blue 9/11/13 7:12
ID999 Mary Blue 9/12/13 14:55

I have result as above.
How can I only take in latest blue color LogDate escalated?
Using Row number?

Result expected as below:

ID Agent Color LogDate
ID999 Lorren Red A 8/31/13 16:46
ID999 Mary Blue 9/3/13 12:49
ID999 Mary Blue 9/9/13 14:57
ID999 Sarah Red 9/9/13 15:23
ID999 John Red 9/10/13 1:21
ID999 John Red 9/10/13 1:21
ID999 Mary Blue 9/11/13 7:12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 23:05:11

your output makes no sense

why you selected both records for Mary Blue at

ID999 Mary Blue 9/3/13 12:49
ID999 Mary Blue 9/9/13 14:57

but selected only one ie
ID999 Mary Blue 9/11/13 7:12
out of these?

ID999 Mary Blue 9/11/13 7:12
ID999 Mary Blue 9/11/13 7:12
ID999 Mary Blue 9/12/13 14:55

Tell us your full set of rules please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-10-07 : 23:31:23

i only would like to see the latest escalated:

red-->blue-->red
or
blue-->red-->blue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 23:39:39

quote:
Originally posted by peace

i only would like to see the latest escalated:

red-->blue-->red
or
blue-->red-->blue

latest escalated on basis of what? its not definitely date that you're considering here as some cases you return one out of group and some cases you return all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources