Getting records from a table based on a filter field and Between but also having the OR logic for multiple rows

Question

I have a this table, where I store multiple ids and an age range (def1,def2)

CREATE TABLE "template_requirements" (
  "_id" INTEGER NOT NULL,
  "templateid" INTEGER,
  "def1" VARCHAR(255),
  "def2" VARCHAR(255),
  PRIMARY KEY("_id")
)

Having values such as:

templateid | def1 | def2
-------------------------------
100        | 7    | 25
200        | 40   | 90
300        | 7    | 25
300        | 40   | 60

As you see for templateid 300, we have an or logic: age between 7 and 25 or age between 40 and 60.

I want to get all the template ids that are not for a certain age like 25... What's the problem?

If I run a query like this one:

SELECT group_concat(templateid) 
 FROM template_requirements 
where 1=1 and '25' not between cast(def1 as integer) 
                       and cast(def2 as integer)

it returns 200, 300, which is wrong, as the 300 matched on row 40 to 60, but shouldn't be included in the result as we have a condition with same templateid 7 to 25 that fails the not beetween stuff.

How would be the correct query in SQLite, I would like to keep the group_concat stuff.

Answer 1

You might try EXCEPT:

sqlite> select group_concat(templateid)
          from (select templateid from template_requirements
                except
                select templateid from template_requirements
                 where 25 between cast(def1 as integer) and cast(def2 as integer));

... show me those "templateid"s except those which have an age range encompassing 25.

Or how about a similar WHERE NOT IN:

sqlite> select group_concat(distinct templateid)
          from template_requirements
         where templateid not in
                 (select templateid from template_requirements
                   where 25 between cast(def1 as integer) and cast(def2 as integer));

Both are fairly "colloquially accurate" SQL representations of what you're trying to do...

Answer 2

I think i'ts not sqlite but the query, try this:

SELECT templateid
FROM template_requirements 

/*You want this values*/
WHERE '25' not between cast(def1 as integer) and cast(def2 as integer)

/*You don't want results with this values (with this you should eliminate 300)*/
AND templateid NOT IN (
  SELECT templateid
  FROM template_requirements 
  WHERE '25' between cast(def1 as integer) and cast(def2 as integer)
  )