Query for multiple values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Query for multiple values

Post 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:
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

IN works for 'or' relationships.. 'pairs' implies an 'and' relationship... somewhat different requires there..
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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.)
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
Post Reply