Would not the following snippet be useful to return the record ID if the
feature set already exists?
SELECT rec.id
FROM records rec
WHERE
1 = (SELECT SUM(id_f IN (21, 22, 23)) = 3 AND COUNT(*) = 3 FROM
records_features rf WHERE rf.id_r = rec.id)
You would have to have some logic in your calling
On Sat, 16 Apr 2016 01:20:55 +0200
Ketil Froyn wrote:
> I have two tables and a join table, in principle like this:
>
> CREATE TABLE records (id INTEGER PRIMARY KEY, data TEXT);
> CREATE TABLE features (id INTEGER PRIMARY KEY, data TEXT UNIQUE);
> CREATE TABLE records_features (id_r INTEGER,
On 2016/04/16 3:03 AM, R Smith wrote:
> Actually, this would achieve the same, without the extra table:
No it won't work this way at all, I misjudged the outcome. That's what I
get for not testing it - Apologies!
Actually, this would achieve the same, without the extra table:
IF NO_ROWS_ARE_RETURNED_FOR (
SELECT 1 FROM (
SELECT id_r, GROUP_CONCAT(id_f) AS combo
FROM records_features
WHERE id_r = 10
GROUP BY id_r
) WHERE combo = '20,21,22'
) THEN {
INSERT INTO
I would simply use a fourth table that is essentially itself just an
Index, say:
CREATE TABLE records_features_u (
id_r INT NOT NULL,
combo_features TEXT NOT NULL,
PRIMARY KEY (id_r, combo_features)
) WITHOUT ROWID;
IF SUCCEED(
INSERT INTO combo_features_u 10, "20,21,22";
) THEN {
I have two tables and a join table, in principle like this:
CREATE TABLE records (id INTEGER PRIMARY KEY, data TEXT);
CREATE TABLE features (id INTEGER PRIMARY KEY, data TEXT UNIQUE);
CREATE TABLE records_features (id_r INTEGER, id_f INTEGER, ord INTEGER);
A record consists of one or more
6 matches
Mail list logo
