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.
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'
I just want the other rows that match the same date and ID when there's a blue type.
Correct result would be:
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.