We have a table that has 10 fields that are used as in query as a key. The fields in question are M1-M10. M1-M10 will contain an integer of 0-5000 (may be higher). Also the values in fields M1-M10 are always unique within the record and are usually ascending (we can mandate this if it will help). An exception to this is the value of 0 or NULL which means the field is not used and if a field is 0 all higher fields will be 0 ex: M4 = 0 then M5-10 will also be 0 (sometimes this can be null, but we can eliminate the nulls) and of course M1 will never be 0. There is no guarantee that 2 or more records will not have the same values for fields M1-M10. So an example of fields from those records would be.
1,7,11,15,0,0, 0, 0, 0, 0 3,11,22,100,0,0,0,0,0,0,0 2,150,0,0,0,0,0,0,0 7,15,0,0,0,0,0,0,0,0 We now need to query these to find all records that have only values from within a specific set. The current query we use which does work is similar to the following. Select * FROM TABLE where ((M1 IN (0, 1,2,7,15,150)) AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL ) AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL) AND (M3 IN (0, 1,2,7,15,150) Or M3 IS NULL) AND (M4 IN (0, 1,2,7,15,150) Or M4 IS NULL) AND (M5 IN (0, 1,2,7,15,150) Or M5 IS NULL) AND (M6 IN (0, 1,2,7,15,150) Or M6 IS NULL) AND (M7 IN (0, 1,2,7,15,150) Or M7 IS NULL) AND (M8 IN (0, 1,2,7,15,150) Or M8 IS NULL) AND (M9 IN (0, 1,2,7,15,150) Or M9 IS NULL) AND (M10 IN (0, 1,2,7,15,150) Or M10 IS NULL)) This query works and has reasonable performance right now for us, but I feel like there should be a more efficient way to do this. Thanks Chris _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users