SQL: find records in a 1: n ratio that do not match a condition spanning multiple rows

Question

I am trying to optimize SQL queries in Akonadi and have encountered the following problem, which seems to be not easy to solve with SQL, at least for me:

Suppose 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 records in awhich there are no records namein the table b. For instance. I need to make sure that each entry ahas at least a namerecording "foo"and "bar"in the table b. Therefore, 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 paramount.

One MySQL solution:

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|%';

, a b. , SQLite PostgreSQL, , GROUP_CONCAT .

, .

Answer 1

, , ... , .

Answer 2

RDBMS SQL:

SELECT 
   a.a_id, 
   Foo.b_id as Foo_Id,
   Bar.b_id as Bar_Id
FROM a
LEFT OUTER JOIN (SELECT a_id, b_id FROM b WHERE name = 'foo') as Foo ON
   a.a_id = Foo.a_id
LEFT OUTER JOIN (SELECT a_id, b_id FROM b WHERE name = 'bar') as Bar ON
   a.a_id = Bar.a_id
WHERE
   Foo.a_id IS NULL
   OR Bar.a_id IS NULL

Answer 3

, , . :

CREATE TABLE required(name varchar(255) primary key);
INSERT INTO required VALUES('foo'), ('bar');

( , )

, b, :

SELECT a.a_id, required.name FROM a CROSS JOIN required;

, b, , , :

SELECT a.a_id, required.name, b.b_id
FROM a
     CROSS JOIN required
     LEFT JOIN b ON b.a_id = a.a_id AND b.name = required.name;

, :

SELECT a.a_id, required.name
FROM a CROSS JOIN required
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.a_id AND b.name = required.name);

, b (a_id, name), , (, , ). b .

Answer 4

#sql freenode Ari-Ugwu Xgc: CrossTab:

SELECT a.a_id, SUM(name = "foo") as hasFoo, SUM(name = "bar") as hasBar, ...
  FROM a
  LEFT JOIN b USING (a_id)
  GROUP BY a.a_id
  HAVING hasFoo < 1 OR hasFoo IS NULL OR hasBar < 1 OR hasBar IS NULL...;