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

Reply via email to