how to select a row that doesnt have a specific value
-
7 มีนาคม 2555 1:00
HI I have a table in which I have a forgeign key ClientiD and there is another bit column current.
There could be multiple record with the client ID and only 1 could have current =1 . but not all clients will have current=1.
how i select the clientID for which there is current=0
For example
In the below table I want to get the clients that does not have currrent=1 . I have client id 23 and client id 24. that doesnt have current =1. how i write tsql to bring up 23 and 24.
RecID ClientID current
1 23 0
2 23 0
3 23 0
4 18 1
5 18 0
6 18 0
7 24 1
8 24 0
9 24 0
10 25 0
11 25 0
i am a novice and a student
ตอบทั้งหมด
-
7 มีนาคม 2555 1:04ผู้ดูแล
Try
select ClientID from ClientsInfo GROUP BY ClientID HAVING MAX(CAST(Current as int)) = 0
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- เสนอเป็นคำตอบโดย Vinay Valeti 7 มีนาคม 2555 3:33
- ทำเครื่องหมายเป็นคำตอบโดย Kalman TothMicrosoft Community Contributor, Moderator 12 มีนาคม 2555 19:13
-
7 มีนาคม 2555 1:32
select distinct ClientID from yourtable where ClientID not in (select ClientID from yourtable where curr = 1)
Sri Krishna -
7 มีนาคม 2555 1:41
select distinct ClientID from yourtable where ClientID not in (select ClientID from yourtable where curr = 1)
Sri KrishnaPlease use EXISTS instead of IN (or in your example NOT EXISTS) - http://msdn.microsoft.com/en-us/library/ms188336.aspx
When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript -
16 มีนาคม 2555 6:39
Thaks for all your answer. I try to do the code as it says.I did get the correct result. but i was thinking what did that MAX did . I know it is to check for the max value .
So what is max in current is 1 not 0. Just wanted to understand that piece of code Having MAX(CAST(Current as int))=0
i am a novice and a student
-
16 มีนาคม 2555 13:13ผู้ดูแลThe Current can be either 1 or 0. That condition ensures, that all Current = 0 as we're testing for max = 0. I cast to int assuming that current is a bit column and we can not use aggregate functions on the bit column, we need to convert them to int (or tinyint) first.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
.png)
