Sql-server – Return ALL rows based on a matching date also matching a specific value in another column

sql serversubquery

I'm using SQL Server 2014, exact version: 12.0.5579.0.

I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just takes those dates unattached to its row ID and type and applies it to the outer query.

Example query:

SELECT A.ID, A.date1, A.type 
FROM table1 A 
WHERE A.ID IN (SELECT B.ID FROM table1 B WHERE B.type='blue') 
AND A.date1 IN (SELECT B.date1 FROM table1 B WHERE B.type='blue') 
AND type <> 'blue'

Example table:

enter image description here

I just want the other rows that match the same date and ID when there's a blue type.

Correct result would be:

enter image description here

Incorrect result:

enter image description here

It falsely included a row with ID 102 since 102 also has a row with the same date (1/1/2019) as the others, but not blue. If 102 had a blue type on 1/1/2019 then the date could match the green type row returned, but it doesn't.

What sort of subquery should I be doing? Seems simple at first but I've gotten myself into a mess! Can't figure out how to return row values that match values in two different columns together.

Best Answer

It seems like you are trying to implement this logic:

SELECT
  A.ID, A.date1, A.type 
FROM
  table1 A 
WHERE
  (A.ID, A.date1) IN (SELECT B.ID, B.date1 FROM table1 B WHERE B.type='blue') 
  AND A.type <> 'blue'
;

However, SQL Server currently does not support tuple comparison. One common solution in such cases is to rewrite the IN predicate as an equivalent EXISTS predicate:

SELECT
  A.ID, A.date1, A.type 
FROM
  table1 A 
WHERE
  (A.ID, A.date1) IN (SELECT B.ID, B.date1 FROM table1 B WHERE B.type='blue') 
WHERE
  EXISTS
  (
    SELECT *
    FROM table1 B
    WHERE B.type='blue'
      AND B.ID = A.ID
      AND B.date1 = A.date1
  )
  AND A.type <> 'blue'
;

One other option, which would resemble the IN predicate a little closer, would be to use comparison with the help of the INTERSECT set operator:

SELECT
  A.ID, A.date1, A.type 
FROM
  table1 A 
WHERE
  EXISTS
  (
    SELECT A.ID, A.date1
    INTERSECT
    SELECT B.ID, B.date1 FROM table1 B WHERE B.type='blue'
  )
  AND A.type <> 'blue'
;

Note, though, that in this case the semantics would slightly differ, because when comparing values using INTERSECT, SQL Server treats two null values as equal, whereas the IN predicate follows the behaviour of the = operator, which evaluates comparison of two null values as "Unknown" rather than "True" or "False". Therefore, this second method would be equivalent only under the assumption that neither ID nor date1 can ever be null.

Related Question