NOT IN based on multiple columns?

Question

I have a table that looks like this:

CREATE TABLE public.mytable (
  p_id varchar(40) NOT NULL,
  j_id varchar(48) NOT NULL,
  u_id varchar(255) NOT NULL
);

I'm using the following to find rows based on p_id and j_id:

SELECT * FROM mytable WHERE p_id = ? AND j_id = ? LIMIT 10

I want to extend this query so that it also excludes some rows, for example, I have a list like this:

class MyEntity {
    public p_id 
    public j_id;
    public u_id;
}

List<MyEntity> rowsToExclude = new ArrayList<>();
rowsToExclude.add(new MyEntity(1, 3, 5));
rowsToExclude.add(new MyEntity(7, 8, 9));

How do I feed the rowsToExclude into the query, for example in SQL it would look something like this:

SELECT * 
FROM mytable 
WHERE p_id = ? AND j_id = ?
  and (p_id, j_id, u_id) not in ((?, ?, ?), (?, ?, ?))
LIMIT 10

I found this reference that was the closest to what I need but not quite:

https://stackoverflow.com/a/55921388/12177456

handle.createQuery("SELECT value FROM items WHERE kind in (<listOfKinds>)")
      .bindList("listOfKinds", keys)
      .mapTo(String.class)
      .list();

// Or, using the 'vararg' definition
handle.createQuery("SELECT value FROM items WHERE kind in (<varargListOfKinds>)")
      .bindList("varargListOfKinds", "user_name", "docs", "street", "library")
      .mapTo(String.class)
      .list();

Does this answer your question? [Select NOT IN multiple columns](https://stackoverflow.com/questions/8033604/select-not-in-multiple-columns) — EJoshuaS - Stand with Ukraine, May 05 '21 at 16:44
[Another possibility](https://stackoverflow.com/questions/48646568/sql-server-join-where-not-exist-on-other-table) - could you load the values you want to exclude into a temp table and exclude them in a join or NOT EXISTS subquery? — EJoshuaS - Stand with Ukraine, May 05 '21 at 16:48

score 1 · Answer 1 · answered May 05 '21 at 16:57

One option would be to load the entities to exclude into a temp table and do a LEFT JOIN like the one suggested in this answer. Something like the following (fair warning: I'm not in front of an IDE so the syntax may not be perfect):

SELECT m.*
from mytable m
LEFT JOIN #tmpTbl t
ON m.p_id = t.p_id and m.j_id = t.id and m.u_id = p.u_id
-- t.id is auto-generated
WHERE t.id IS NULL and m.p_id = ? AND m.j_id = ?

I'm assuming here that you've created a temp table named #tmpTbl (which has an auto-generated ID) and loaded your list of exclusions into it.

NOT IN based on multiple columns?

1 Answers1