CODEWITHSUNDEEP
sqlsql-server
Vũ Trần
Vũ Trần

Reputation: 71

How to make a condition to select A and B in a column but not A or B alone?

I have a table A:

col1    col2
nameA   A
nameA   B
nameB   A
nameC   B
.........

I want to make a condition so that all the values of nameA with both A and B, but not only A or B itself, will be selected. How can I do this?

Using IN is not working since if only A or B then the condition is still giving result.

Expected result:

col1    col2
nameA   A
nameA   B

Not expected resut:

col1    col2
nameA   A

Or

col1   col2
nameA  B

Upvotes: 0

Views: 1195

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Assuming no duplicates in your table, window functions are an easy solution:

select t.*
from (select t.*, sum(case when col2 in ('A', 'B') then 1 else 0 end) as cnt
      from t
     ) t
where cnt = 2;

This nicely generalizes to more than two columns.

Upvotes: 0

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

DECLARE @Test TABLE (
    col1    VARCHAR(32),
    col2    VARCHAR(1)
)

INSERT @Test (col1, col2)
VALUES
('nameA', 'A'),
('nameA', 'B'),
('nameB', 'A'),
('nameC', 'B')

SELECT col1, col2
FROM @Test t
WHERE EXISTS (
    SELECT 1
    FROM @Test
    WHERE col1 = t.col1
        AND col2 != t.col2
)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

You may try the following aggregation query:

WITH cte AS (
    SELECT col1
    FROM yourTable
    WHERE col2 IN ('A', 'B')
    GROUP BY col1
    HAVING MIN(col2) <> MAX(col2) AND COUNT(*) = 2
)

SELECT col1, col2
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM cte t2 WHERE t1.col1 = t2.col1);

As a note about the aggregation logic in the CTE, I restrict to only col1 groups which have only A and B values for col2 and only groups having exactly two records (i.e. multiple A and B is not acceptable). With the CTE having done most of the heavy lifting, to find the full matching records we only need a simple select against your table with an EXISTS clause.

Upvotes: 2

arnaud
arnaud

Reputation: 1

where col1 = 'nameA' and (col2 = 'A' or col2 = 'B')

Upvotes: -1

Related Questions