Geek Answers Handbook

SQL search to filter identifier list

I am curious if there is a tricky way to accomplish the following only through SQL. I have an id list from one database and I want to filter this list with another database / table. Requirements:

  • Search the table to match identifiers; if there is a match, and this entry meets another restriction (where field2 is null), then remove it from the original list.
  • Return the results from (1), as well as any identifier in the original list that was not found in the second table.

For example, if my list contains id [1,2,3,4], and my_table, which I want to filter, looks like this:

+-------+--------+
| my_id | Field2 |
+-------+--------+
|     1 | true   |
|     2 |        |
|     3 | true   |
+-------+--------+

Then I expect the final result to be [1,3,4]. The record with id = 2 is filtered out because field 2 is zero and 4 remains because it is not in the table at all.

So far, everything that I have come up with is below, which meets the requirement (1), but not (2):

select distinct my_id
from my_table where my_id IN (1,2,3)  --csv list of id's
and not exists 
(select my_id
   from my_table 
   where my_id IN (1,2,3) and field2 is null)

Is it possible to use MINUS in some way by creating a temporary entry from my original id list?

+4
source share
1 answer
SELECT DISTINCT t1.my_id
FROM my_table t1
LEFT JOIN my_table2 t2 
ON t1.my_id = t2.field2
WHERE t1.my_id IN (1,2,3)
  AND t2.field2 IS NULL
+1
source

More articles:


All Articles