SQL: find entries in 1:n relation that don't comply with condition spanning multiple rows
- by milianw
I'm trying to optimize SQL queries in Akonadi and came across the following problem that is apparently not easy to solve with SQL, at least for me:
Assume the following table structure (should work in SQLite, PostgreSQL, MySQL):
CREATE TABLE a (
a_id INT PRIMARY KEY
);
INSERT INTO a (a_id) VALUES (1), (2), (3), (4);
CREATE TABLE b (
b_id INT PRIMARY KEY,
a_id INT,
name VARCHAR(255) NOT NULL
);
INSERT INTO b (b_id, a_id, name)
VALUES (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'asdf'),
(4, 2, 'foo'), (5, 2, 'bar'), (6, 3, 'foo');
Now my problem is to find entries in a that are missing name entries in table b. E.g. I need to make sure each entry in a has at least the name entries "foo" and "bar" in table b. Hence the query should return something similar to:
a_id = 3 is missing name "bar"
a_id = 4 is missing name "foo" and "bar"
Since both tables are potentially huge in Akonadi, performance is of utmost importance.
One solution in MySQL would be:
SELECT a.a_id,
CONCAT('|', GROUP_CONCAT(name ORDER BY NAME ASC SEPARATOR '|'), '|') as names
FROM a
LEFT JOIN b USING( a_id )
GROUP BY a.a_id
HAVING names IS NULL OR names NOT LIKE '%|bar|foo|%';
I have yet to measure the performance tomorrow, but severly doubt it's any fast for tens of thousand of entries in a and thrice as many in b. Furthermore we want to support SQLite and PostgreSQL where to my knowledge the GROUP_CONCAT function is not available.
Thanks, good night.
