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.
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);