SQL Server : SELECT when 2 values exist in rows

user_12033702 0
Viewed 131

I have a SQL query result

col1 col2
A1 C1
A1 C2
A2 C1
A3 C1
A3 C2

This is the SQL

SELECT 
    col1, col2 
FROM 
    table 
WHERE 
    col2 = 'C1' OR col2 = 'C2' 
GROUP BY 
   col1, col2

Now the question is, how can I get a result where col2 must have both C1 and C2 values, like this (As you can see the A2 is not in the list, since it doesn't have the 'C2' unlike the 'A1' and 'A3')

col1 col2
A1 C1
A1 C2
A3 C1
A3 C2

Update : If col1 and col2 are from 2 table

SELECT  
    table.col1, table2.col2  
FROM  
    table  
INNER JOIN   
    table2  
ON  
    table2.col3 = table.col3  
WHERE  
    table2.col2 = 'C1'  
OR  
    table2.col2 = 'C2'  
GROUP BY  
    table.col1, table2.col2
tsql sql-server sql
5 Answers

One way to do this if you are using MySQL 8+:

WITH cte AS (
    SELECT *, MIN(col2) OVER (PARTITION BY col1) min_col2,
              MAX(col2) OVER (PARTITION BY col1) max_col2
    FROM yourTable
)

SELECT col1, col2
FROM cte
WHERE min_col2 = 'C1' AND max_col2 = 'C2';

Note that this answers assumes that C1 and C2 are the only two possible values for col2. If there could be other values, then the above logic would have to change slightly.

user_18632290

You can do:

select col1, col2 
from t 
where col1 in (
  select col1 from t where col2 in ('C1', 'C2') group by col1 
  having count(distinct col2) = 2
)
order by col1, col2
user_64361910

You'll need to determine which groups of rows (grouped by col1) have both C1 and C2 present - there are many ways to do that but one approach is to GROUP BY col1 and use COUNT for 'C1' and 'C2' separately (not together, because I assume a group could have two 'C1' values and zero 'C2' values) - then do an INNER JOIN on col1 in valid groups, like so:

SELECT
    t.*
FROM
    tbl AS t
    INNER JOIN
    (
        SELECT
            col1
        FROM
            tbl
        GROUP BY
            col1
        HAVING
            COUNT( CASE WHEN col2 = 'C1' THEN 1 END ) >= 1
            AND
            COUNT( CASE WHEN col2 = 'C2' THEN 1 END ) >= 1
    ) AS q ON t.col1 = q.col1

Update:

The OP posted a comment reply with this query (below) and asked how to use the above technique with it:

SELECT
    table.col1,
    table2.col2
FROM
    table
    INNER JOIN table2 on table2.col3 = table.col3
WHERE
    table2.col2 = 'C1'
    OR
    table2.col2 = 'C2'
GROUP BY
    table.col1,
    table2.col2

The solution is to use a CTE because that query's results will need to be used twice (because we do an INNER JOIN on itself):

WITH cte AS
(
    -- This is the original query the OP provided in the comment reply. It is unmodified.
    SELECT
        table.col1,
        table2.col2
    FROM
        table
        INNER JOIN table2 on table2.col3 = table.col3
    WHERE
        table2.col2 = 'C1'
        OR
        table2.col2 = 'C2'
    GROUP BY
        table.col1,
        table2.col2
    --
)
SELECT
    t.*
FROM
    cte AS t
    INNER JOIN
    (
        SELECT
            col1
        FROM
            cte
        GROUP BY
            col1
        HAVING
            COUNT( CASE WHEN col2 = 'C1' THEN 1 END ) >= 1
            AND
            COUNT( CASE WHEN col2 = 'C2' THEN 1 END ) >= 1
    ) AS q ON t.col1 = q.col1
user_1591450

You can use window function count as follows:

Select col1, col2 from
(Select col1, col2,
       Count(distinct col2) over (partition by col1) as cnt
  From t
 Where col2 in ('C1', 'C2')) t
Where cnt = 2;

You can also use exists as follows:

Select col1, col2 
  From t
 Where t.col2 in ('C1', 'C2')
   And exists (select 1 from t tt
                Where tt.col1 = t.col1
                  And tt.col2 in ('C1', 'C2')
                  And t.col2 <> tt.col2)
user_115656290

If there are no duplicate rows, I would recommend window functions:

select col1, col2
from (select t.*,
             sum(case when col2 in ('C1', 'C2') then 1 else 0 end) over (partition by col1) as cnt
     from t
    ) t
Where cnt = 2;

Note: This works even when col2 has values other than the two mentioned. And in that case, it returns all the rows.

This is how I interpret the question.

You can also phrase this using subqueries in the where clause. One method uses exists:

select col1, col2
from t t1
where exists (select 1
              from t t2
              where t2.col1 = t1.col1 and t2.col2 = 'C1'
             ) and
      exists (select 1
              from t t2
              where t2.col1 = t1.col1 and t2.col2 = 'C2'
             ) ;

This will work with duplicates as well as with other columns. I prefer exists but you can express this also as:

select col1, col2
from t t1
where (select count(distinct t2.col2)
       from t t2
       where t2.col1 = t1.col1 and
             t2.col2 in ('C1', 'C2')
      ) = 2;
user_11440350
Related