phil phil -4 years ago 49
SQL Question

How to get max of "not exists" query

I'm not super great with SQL, but I'm using it for a project. Below is the query I would like to write, but of course it isn't valid SQL

select * from a
where not exists (
select * from b
where a.name = b.name) common
where a.id > max(common.id)


My goal is to get the rows in
a
that do not join with those in
b
, but only those with a greater
id
than any of the ones that do join. The point of this is so that I can begin filling a database with values, stop, and then continue later where I left off.

I'm using sqlite with python; I know I could do two queries with python, but I'm guessing there is a way to do it with SQL (and I'm assuming that's 'better')

Dump:

BEGIN TRANSACTION;
CREATE TABLE "a" (
`Id` INTEGER NOT NULL UNIQUE,
`Name` TEXT,
PRIMARY KEY(`Id`)
);
INSERT INTO a VALUES(16,'Bob');
INSERT INTO a VALUES(17,'George');
INSERT INTO a VALUES(18,'Jimmy');
INSERT INTO a VALUES(19,'Billy');
INSERT INTO a VALUES(20,'Johnny');
INSERT INTO a VALUES(21,'James');
INSERT INTO a VALUES(22,'Bart');

CREATE TABLE "b" (
`Id` INTEGER NOT NULL UNIQUE,
`Name` TEXT NOT NULL,
PRIMARY KEY(`Id`)
);
INSERT INTO b VALUES(16,'Bob');
INSERT INTO b VALUES(19,'Billy');
COMMIT;


There are other columns, so these aren't identical tables, but the query should get Johnny, James, and Bart from table
a
.

Answer Source

I propose:

  • make a convenient common table expression for the inner join (first line)
  • select all from a which are not in ab (second line and fourth line)
  • restrict according to desired condition (third line)

Note that the ids from ab are identical to those from a which join with b. So their max is the max from ids in a which join.

I simply output *, using only the desired fields is possible should be easy.

Code:

with ab(id, field) as (select id, a.name from a join b using(id))
select * from a
where id > (select max(id) from ab)
except select * from ab;

Output:

20|Johnny
21|James
22|Bart
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download