TSQL How to select value based on another value

Question

So I am trying to select one value based off the value of another column. For example, in the below table rows, I would like to select the ID column where none of that specific value has the value of "Transferred" for the Status column. So with the below rows, only the value "DEF" would be returned for the query. I can't figure out how to do this.

ID                    Status  
====                  ===========
ABCD                  Received    
ABCD                  Transferred    
XYZ                   Received
XYZ                   Transferred
DEF                   Received

I'm voting to close this question as off-topic because it is not a question "for professional and enthusiast programmers" this person is a beginner and needs a beginner tutorial. — Ben, Apr 09 '18 at 16:45
Ben - First, this question is not off-topic. It directly relates to SQL and TSQL. Not sure why you would suggest it is off topic. And second, you're acting as if this is a trivial question, which it is not. I am doing a query of a much larger level, and I simplified it greatly to allow for easy understanding of others and to help in finding a solution. — user3465260, Apr 09 '18 at 16:58
Yea I wouldn't really consider this a trivial question (and I also don't agree with Ben that "professional and enthusiast" does not include beginner; enthusiast = beginner in my mind). However, I believe Ben just misread/misunderstood your question because admittedly, it even took me a couple read-throughs to understand that you're not just looking for a SELECT * FROM table WHERE [Status] <> 'Transferred' — SUMguy, Apr 09 '18 at 21:46
The first one-page tutorial answers it. There must be at least a thousand equivalent questions on SO, making it impossible to find a duplicate. But no: Ask, Answer, 10 Points! This question is everything that is wrong with Stack Overflow at the moment. — Ben, Apr 10 '18 at 08:56

SQLChao · Answer 1 · 2018-04-09T17:01:22.743

1

You can try using NOT IN

   SELECT ID
    FROM YourTable 
    WHERE ID NOT IN (SELECT ID FROM YourTable WHERE Status = 'Transferred')

Another option using NOT EXISTS

SELECT DISTINCT id 
FROM yourtable yt1
WHERE NOT EXISTS (SELECT * FROM yourtable yt2 WHERE yt1.id = yt2.id AND Status='Transferred')

edited Apr 09 '18 at 17:01

answered Apr 09 '18 at 16:56

SQLChao

7,709
1
17
32

score 0 · Answer 2 · edited Apr 09 '18 at 16:56

0

Give this a whirl..

SELECT DISTINCT id 
FROM your_table_name
WHERE NOT EXISTS (SELECT id FROM your_table_name WHERE Status='Transferred')

edited Apr 09 '18 at 16:56

SQLChao

7,709
1
17
32

answered Apr 09 '18 at 16:55

Jim Horn

879
6
14

Since the subquery isn't correlated with the outer query, this may not work as you expect. See SQLChao's answer for a correlated version. – HABO Apr 09 '18 at 19:23
Ha. You may be correct. I had WHERE id NOT IN ( but for some reason edited it to WHERE NOT exists. – Jim Horn Apr 09 '18 at 19:27

score 0 · Answer 3 · answered Apr 09 '18 at 17:27

Another possible solution:

declare @t table (ID varchar(4), Status varchar(11))
insert @t (ID, Status) VALUES 
('ABCD','Received'),
('ABCD','Transferred'),
('XYZ','Received'),
('XYZ','Transferred'),
('DEF','Received')    

select [ID], p.Received, isnull(p.Transferred,0) Transferred
FROM (select id, Status, count(1) cnt from @t group by id, status) t
PIVOT
(
     sum(cnt) for status in (Received,Transferred) 
) AS p
where Transferred is null or Transferred = 0

This has the advantage of making all of the statuses available in your where clause. I suspect it is the least efficient of the 3 answers thus far, but it could be useful.

TSQL How to select value based on another value

3 Answers3