Page 1 of 1

Query for multiple values

Posted: Fri Jul 09, 2004 10:31 am
by Bill H
Okay, this is admittedly a bad design, but the application was totally unforseen.

Lets say we have 3000 records and we need to query for records with two fields containing any one of an unknown but substantial number of pairs of non-contiguous integer values, for the sake of discussion lets say 20 to as many as 40.

That is "WHERE Cref=80 AND Seq=9" for instance.

One would really like to avoid 20-40 queries, or one query with 20-40 "OR" clauses in it, but is there any way to do that? Something like matching against an array?
:roll:

Posted: Fri Jul 09, 2004 3:45 pm
by lostboy
Depending on the field types, which are not mentioned, FULL TEXT MATCHing may be useful...

Otherwise the IN clause or wildcard operators may be useful...

Would need some info on the table structure and sample data

Posted: Fri Jul 09, 2004 4:07 pm
by feyd
IN works for 'or' relationships.. 'pairs' implies an 'and' relationship... somewhat different requires there..

Posted: Fri Jul 09, 2004 8:13 pm
by Bill H
Cref and Seq and int columns. The query would be

Code: Select all

WHERE (Cref=80 AND Seq=9) OR (Cref=23 AND Seq=14) OR ...
up to, maybe 40+ pairs.

Posted: Sat Jul 10, 2004 7:59 am
by Bill H
Otherwise the IN clause...
Where can I fine information about that? The MySQL documention under SELECT does not refer to it, and a search turns up nothing.

Posted: Sat Jul 10, 2004 11:55 pm
by fractalvibes
So you basically wanting to say:
....
WHERE Cref= <> (Seq +1)
AND Cref <> (Seq - 1)

so that the values of Cref and Seq are different by more than 1?

fv

Posted: Sun Jul 11, 2004 11:15 am
by Bill H
No, the numbers are completely arbitrary. They could even be the same.
The first will range from 1 to 299,999,999 and the second from 1 to 16.
(And they are in no way related one to the other.)

Posted: Sun Jul 11, 2004 10:03 pm
by fractalvibes
Hmmm...well if the distinct pairs of Cref and Seq can be determined, perhaps you could create a domain table of the specific Cref and Seq values you seek.

fv