SELECTING with multiple conditions on different columns

Question

Alright, I believe I'm on the right track but having some difficulty getting the desired results. I'm attempting to check two different columns in a table for a specific flag, then relating said flag to the second column.

For example, I have said table:

ID   date       flag      flag_id 
-----------------------------------
1  09/25/2017   NO        0001
2  09/25/2017   OTHER     0002
3  09/25/2017   NO        0002
4  09/25/2017   OTHER     0003
5  09/25/2017   OTHER     0004
6  09/25/2017   NO        0005
7  09/25/2017   OTHER2    0005
8  09/25/2017   OTHER     0006

What is needing to be output is the lines that contain repeat flag_id's and only ones containing NO in their flag column, excluding the NO line itself(so only the lines with data that does not show NO). So in this case only output lines 2 and 7. I have already written something that would look like this:

SELECT distinct
   t1.ID,
   t1.date,
   t1.flag,
   t1.flag_id,
FROM table1 t1
WHERE (SELECT COUNT(*)
   FROM table1 t2
   WHERE t1.flag_id = t2.flag_id
   AND t1.flag != 'NO'
   AND t2.flag = 'NO')>1

Making a beginner mistake here, and mind you I am still very new to this, so an explanation on why this is not working or why something else works would be appreciated.

I do not see how it's even possible for you to get a different ID for the results there unless you're running a different query or I'm misunderstanding your issue. — ZLK
– ZLK, Commented Sep 25, 2017 at 23:57
Reformatted and removed example, this should make more sense now since I'm only trying to get the result listed above. :) — samtanderson
– samtanderson, Commented Sep 26, 2017 at 0:05

cha · Accepted Answer · 2017-09-26 00:01:35Z

1

You can use EXISTS instead:

SELECT 
   t1.ID,
   t1.date,
   t1.flag,
   t1.flag_id
FROM table1 t1
WHERE exists (SELECT 1
       FROM table1 t2
   WHERE t1.flag_id = t2.flag_id
   AND t1.flag != 'NO'
   AND t2.flag = 'NO')

Also works when you have multiple different flags:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    ([ID] int, [date] datetime, [flag] varchar(10), [flag_id] int)
;

INSERT INTO Table1
    ([ID], [date], [flag], [flag_id])
VALUES
    (1, '2017-09-25 10:00:00', 'NO', 0001),
    (2, '2017-09-25 10:00:00', 'OTHER', 0002),
    (3, '2017-09-25 10:00:00', 'NO', 0002),
    (4, '2017-09-25 10:00:00', 'OTHER', 0003),
    (5, '2017-09-25 10:00:00', 'OTHER', 0004),
    (6, '2017-09-25 10:00:00', 'NO', 0005),
    (7, '2017-09-25 10:00:00', 'OTHER', 0005),
    (8, '2017-09-25 10:00:00', 'OTHER2', 0005),
    (9, '2017-09-25 10:00:00', 'OTHER', 0006)
;

Query 1:

SELECT 
   t1.ID,
   t1.date,
   t1.flag,
   t1.flag_id
FROM table1 t1
WHERE exists (SELECT 1
       FROM table1 t2
   WHERE t1.flag_id = t2.flag_id
   AND t1.flag != 'NO'
   AND t2.flag = 'NO')

Results:

| ID |                 date |   flag | flag_id |
|----|----------------------|--------|---------|
|  2 | 2017-09-25T10:00:00Z |  OTHER |       2 |
|  7 | 2017-09-25T10:00:00Z |  OTHER |       5 |
|  8 | 2017-09-25T10:00:00Z | OTHER2 |       5 |

edited Sep 26, 2017 at 0:01

answered Sep 25, 2017 at 23:48

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LAS Over a year ago

Yes, this is an efficient way to do this.

LAS · Accepted Answer · 2017-09-26 00:34:25Z

0

Your output doesn't make sense for the query. This returns all rows where the flag it not No but there is another row where the flag is No. Please note that using exists for this is more efficient if this is where the query stops. If you want to expand on the query, this may be better.

SELECT distinct
   t_other.ID,
   t_other.date,
   t_other.flag,
   t_other.flag_id,
FROM table1 t_no
     inner join table1 t_other on t_no.flag_id = t_other.flag_id
WHERE t_no.flag = 'NO' and t_other.flag <> 'NO'

edited Sep 26, 2017 at 0:34

answered Sep 25, 2017 at 23:45

LAS

8195 silver badges7 bronze badges

2 Comments

samtanderson Over a year ago

I do like this answer, now I have a more interesting situation. Say the data set included 3 different flags (NO, OTHER, OTHER2), and I wanted to output all lines with duplicate flag_ids but exclude the lines with NO, how would one go about this? Also assuming the number of flags could be up to 100 and not specifying each flag exactly.

LAS Over a year ago

@maaier I'd recommend trying both solutions. I expect them to return the same answer but cha's answer will run more efficiently because you want to eliminate the No's from the result set.

Collectives™ on Stack Overflow

SELECTING with multiple conditions on different columns

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related