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 combo_features_u 10, "20,21,22"; INSERT INTO records_features VALUES (10,20), (10,21), (10,22); }
On 2016/04/16 1:20 AM, 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, id_f INTEGER, ord INTEGER); > > A record consists of one or more features, and a feature can exist in > multiple records. The "ord INTEGER" is there to keep track of the > order of the features. I want to avoid duplicate feature sets. We can > assume that the ord values will match for two sets. > > So let's say there's a relationship stored in the DB: > > INSERT INTO records (data) VALUES("record1"); > => 10 > INSERT INTO features (data) VALUES("feature1"); > => 20 > INSERT INTO features (data) VALUES("feature2"); > => 21 > INSERT INTO features (data) VALUES("feature3"); > => 22 > INSERT INTO records_features (id_r, id_f, ord) VALUES(10,20,0); > INSERT INTO records_features (id_r, id_f, ord) VALUES(10,21,1); > INSERT INTO records_features (id_r, id_f, ord) VALUES(10,22,2); > > Later, if I come across a new record that has exactly features > "feature1", "feature2" and "feature3" (IDs 20,21 and 22), I don't want > to add a new record for this. So given a new set of feature IDs, how > can I best check if there's another record that has these exact > features before I insert it? > > To be clear, a record with features "feature1", "feature2", "feature4" > would be ok. So would "feature1", "feature2". Subsets are ok, but not > exact duplicates. > > One way could be to drop the join table and do a string concat of the > feature IDs in a UNIQUE column in the record table, ie: > > CREATE TABLE records (id INTEGER PRIMARY KEY, data TEXT, feature_ids > TEXT UNIQUE); > > INSERT INTO records (data, feature_ids) VALUES("record1", "20,21,22"); > > but I'd prefer to avoid manually having to parse the IDs out of a > string. It does appear to be a hairy way to solve what I need though. > > A combination of the two would be better. The string "20,21,22" would > function as a unique key avoiding duplicates, and if I also keep the > join table I can still query the database without parsing strings. I > won't be doing many updates, so I'm not really concerned about editing > these strings. > > But I'm sure there's a better way. Do I need to use a CTE for this? > > -Ketil > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users