mysql - SQL: find entries in 1:n relation that don't comply with condition spanning multiple rows -


I am trying to customize SQL queries in Akonadi and apparently not easy to solve with SQL , At least for me:

Below the following table structure (should work in SQLite, PostgreSQL, MySQL): 

  create table (a_id INT primary key) ; Include one (AID) values ​​(1), (2), (3), (4); Create table B (b_id INT primary key, AIID INT, name VARCHAR tap (255)); Enter the value (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'asdf'), (4, 2, 'foo') b (b_id, a_id, name) Please, (5, 2, 'bar'), (6, 3, 'foo');

Now my problem is finding entries in a , which are missing entries in names table b . Just like I need to make sure that at least names entries "foo" and "bar" in each entry in a b in the table. Therefore, the query should be returned something similar: 

  a_id = 3 is not named "bar" in a_id = 4 "foo" and "bar" name is missing

Since Akon is potentially big in both the tables, the performance is extremely important.

MySQL will have a solution: 

  SELECT a.a_id, CONCAT ('|', GROUP_CONCAT (name ORDER BY NAME ASC SEPARATOR '|'), '|' ) As the name joining an invitation by USING (a_id) GROUP BY a.a_id HAVING names are names or names do not like '%. Bar | Foo | % ';

I have not been assessed yesterday, but there is a different doubt that there are no acceleration for thousands of entries in a and three More than b . Apart from this, we want to support SQLite and PostgreSQL where my knowledge GROUP_CONCAT is not available.

Thank you, Good night.

This should work with any SQL standard RDBMS: 

  SELECT a.a_id, Foo.b_id Foo_Id, bar.b_id as Bar_Id join a left OUTER (SELECT a_id, b_id fROM b WHERE name = 'foo') as Foo ON a.a_id = Foo .a_id left otter (SELECT a_id, b_id fROM b, WHERE name = 'bar') in the form of a.a_id = Bar.a_id WHERE Foo.a_id is null or bar.A_ID IS null

Comments

Post a Comment