Exclude rows based on multiple columns from select query

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, maybe using a NOT IN?

For example, (not real code), something like:

SELECT * FROM mytable WHERE p_id = ? AND j_id = ? 
AND NOT IN(rowsToExclude)
LIMIT 10

Can you use multiple columns for a not in query?

Use the primary key to filter out rows. Does the table have a PK? — The Impaler, May 05 '21 at 14:16
The rows will be deleted after being fetched, this is done in a loop until all rows are deleted, so I don't think an ORDER BY is needed here. — rmf, May 05 '21 at 14:25

score 0 · Answer 1 · answered May 05 '21 at 14:25

0

You can use eight parameters instead of just two.

For example, you can do:

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

I would assume 1, 3, 5, 7, 8, and 9 are strings since the table columns are VARCHAR.

Note: This strategy doesn't work with nulls.

answered May 05 '21 at 14:25

The Impaler

45,731
9
39
76

Thanks, any chance you know how to do this using the jdbi fluent api like this https://stackoverflow.com/a/55921388/12177456 – rmf May 05 '21 at 15:05

Exclude rows based on multiple columns from select query

1 Answers1