1

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!

  • 1
    So if the specific config has any other key-value pair (besides these 2), you want the object excluded from the results? – ypercubeᵀᴹ Sep 26 '18 at 9:41
  • Also: == is not a valid SQL operator. I guess you mean =. – ypercubeᵀᴹ Sep 26 '18 at 9:44
  • Yes, that's exactly what I want. And thanks for the hint - my sqlite swallowed the "==". – Lukas Barth Sep 26 '18 at 9:45
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'
                  )
      ) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.