SQLite JOIN WHERE not in multiple tables

Question

Im using sqlite and have the following schema and table data

"CREATE TABLE numbers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,   
    cli INTEGER UNIQUE
);"

"CREATE TABLE allocated (
    did_id INTEGER,
    cli INTEGER UNIQUE,
    client_id INTEGER
);"

"CREATE TABLE used (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cli INTEGER DEFAULT 0 NOT NULL,
    client_id INTEGER,
    date DATETIME
);"

Example data

table `numbers`
id  cli
11  444
12  555
13  666
14  777
15  888
16  999

table `allocated`
did_id  cli
14  777
12  555

table `used`
id  cli
31  111
31  222
32  444
33  999

I want to get the column data "cli" from numbers table where "cli" in not in table allocated or table used.

expected result

numbers
id cli 
13 666
15 888

First off I like to know what is the sqlite query to for this will be. If possible then I would like to apply it with Medoo php library.

score 2 · Accepted Answer · answered Dec 20 '18 at 01:32

One option is a double anti-join:

SELECT n.id, n.cli
FROM numbers n
LEFT JOIN allocated a
    ON n.cli = a.cli
LEFT JOIN used u
    ON n.cli = u.cli
WHERE
    a.cli IS NULL AND u.cli IS NULL;

We could also use EXISTS here:

SELECT n.id, n.cli
FROM numbers n
WHERE
    NOT EXISTS (SELECT 1 FROM allocated a WHERE a.cli = n.cli) AND
    NOT EXISTS (SELECT 1 FROM used u WHERE u.cli = n.cli);

SQLite JOIN WHERE not in multiple tables

1 Answers1