2

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.

  • 1
    Hi and welcome to DBA.SE. What RDBMS are you using? (SQL Server, Oracle, Mysql,...) Could you add that one to the tags? The table definition and some sample data could also help in getting an answer faster :) – Randi Vertongen May 9 at 22:07
  • you talk about subqueries and outer queries, but you haven't shown a query. There is a huge gap in your question as to how the input table gets to selecting the correct result (i.e. what makes the type red and green, and the ID 100, 101 so special). – danblack May 10 at 5:10
  • Sorry guys - I've updated the question with more info. The SQL database is an emulated from an object oriented database and I believe standard SQL Server. – minnemike May 10 at 14:22
2

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.

  • Thank you! Your first working example brought back a perfect result using my actual data with many more rows of data than my example. The key I was missing were the EXISTS predicate and WHERE inclusion of (B.ID = A.ID AND B.date1 = A.date1) together in a subquery to match a single row of data for both ID and date together. As you mentioned, my roadblock was trying to include tuple comparisons with an IN predicate subquery. EXISTS opened that all up! – minnemike May 13 at 16:22
2

try this

create table #colors (
   ID       int,
   date     date,
   type     varchar(10)
)

insert into #colors values (100, '1/1/2019', 'red')
insert into #colors values (100, '1/1/2019', 'blue')  
insert into #colors values (101, '1/1/2019', 'blue')
insert into #colors values (101, '1/5/2019', 'blue')
insert into #colors values (101, '1/1/2019', 'green')
insert into #colors values (101, '1/9/2019', 'green')
insert into #colors values (102, '1/5/2019', 'blue')
insert into #colors values (102, '1/2/2019', 'green')
insert into #colors values (103, '1/1/2019', 'blue')
insert into #colors values (103, '1/9/2019', 'green')


select c.*
  from #colors c
  join #colors c1
    on c.id = c1.id
   and c1.type = 'blue'
   and c.date = c1.date
 where c.type <> 'blue'
  • Nice work... unfortunately I do not have access to create a table... but I think you are down a path I thought might have to be part of the solution... maybe CREATE VIEW can be used similarly? I may hit a wall here though due to the actual table set containing thousands of rows. I made a simple abstract example of it here for simplicity. – minnemike May 10 at 20:49
  • The logic you need to implement is there, you need not create the table. Review the join logic and see if you can implement that in your environment with your own query. – kevinnwhat May 10 at 20:51
1

Considering the sample table and desired result that you gave.

A query should be along the lines of

SELECT * FROM FOO 
WHERE type != 'blue' 
AND ID IN (SELECT ID FROM FOO WHERE type = 'blue') 
AND date IN (SELECT date FROM FOO WHERE type = 'blue')

There are probably more elegant solutions out there, but hard to pinpoint in what direction without knowing the exact RDBMS. This is also purely on the table data, there's no way to know if the data actually comes from somewhere else or not.

The above query checks if the specified ID's/Dates of non blue types match both the ID/date of a blue type.

  • I've added my failed query and a failed return plus some better explanations above, but you did give me a new angle to go with here. I'll try it out. – minnemike May 10 at 14:26
  • I've updated the tables and description above to be 100% correct and more clear... So, I've tried this and it comes very close but falsely includes a row where the ID 102 has a blue type (on a different date) and a date matching ID 100 (which has a blue type). So this false row meets WHERE criteria with a date of blue type (from ID 100) and blue type (referencing a different date 1/5/2019). In other words, ID and date are considered separate entities and not together from a single row. – minnemike May 10 at 17:12
  • I guess I should say... In other words, ID and date are considered separate entities (in your query example) and not together from a single row. – minnemike May 10 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.