Query for existence of objects across two multi-to-one relationships

Question

I have three relations. The first is "MyObject", which contains an integer "value" attribute as well as a foreign key to "Config". The relationship is "multiple MyObjects to one Config".

The "Config" relation contains basically just a name. Finally, there is the "ConfigKV" relation, having three attributes: a key, a value, and a foreign key to "Config". Each Config has multiple ConfigKVs.

This is the SQL statement to create the database:

CREATE TABLE "MyObject" (
  "value" INTEGER NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

CREATE TABLE "Config" (
  "name" TEXT NOT NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);

CREATE TABLE "ConfigKV" (
  "key" TEXT NOT NULL,
  "value" TEXT NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

I want to query for all MyObjects that:

• has value 42
• the associated Config of which has exactly two ConfigKVs associated:
  • "Foo"->"Bar"
  • "Fuz"->"Baz"

Aside from the "exactly" part above, I can achieve that with this query:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'foo' AND value == 'bar'
) as SQ1 on SQ1.cfg == MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'fuz' AND value == 'baz'
) as SQ2 on SQ2.cfg == MyObject.cfg
WHERE MyObject.value == 42;

The inner joins neatly eliminate all MyObjects that do not have the required Key-Value mappings associated. However, I have no clue how to exclude MyObjects the config of which has a superset of the specified Key-Value mappings. Any idea here?

Thanks a lot!

Answer 1

You can add this restriction with either a NOT EXISTS subquery or with LEFT JOIN / IS NULL:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'foo' AND value = 'bar'
) as SQ1 on SQ1.cfg = MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'fuz' AND value = 'baz'
) as SQ2 on SQ2.cfg = MyObject.cfg
WHERE MyObject.value = 42
  AND NOT EXISTS
      ( SELECT 1
        FROM ConfigKV
        WHERE ConfigKV.cfg = MyObject.cfg
          AND NOT ( key = 'foo' AND value = 'bar'
                 OR key = 'fuz' AND value = 'baz'
                  )
      ) ;