Member
Join Date: Jul 2004
|
i'm trying to SELECT items that MUST match a few individual criteria and MUST match one of many criteria. the problem is, i can't make it work. look below for my attempt, i think you'll understand what i'm trying to do. if someone knows the correct syntax for this, please let me know.
Code:
SELECT * FROM `table` WHERE `one` = '1' AND `two` = '2' AND `monkey` = ('1' OR '2' OR '3') does anyone have any advice? |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
try:
Code:
SELECT * FROM `table` WHERE `one` = '1' AND `two` = '2' AND `monkey` IN ('1', '2', '3') or Code:
SELECT * FROM `table` WHERE `one` = '1' AND `two` = '2' AND ((`monkey` = '1') OR (`monkey` = '2') OR (`monkey` = '3')) I don't have a copy of MySQL handy at the moment, but I know that kind of syntax works in PostgreSQL. |
quote |
Member
Join Date: Jul 2004
|
thanks brad, always the helpful one!
i'll give these a try tomorrow. the second one makes more sense to me, i'm not familiar with the IN command - but whatever works is fine! |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
IN returns true if there's a matching value in the parentheses. It's similar to the in_array function in PHP.
Personally, I prefer using one IN keyword over writing out a bunch of OR operations, but that's just my style. Something really handy about IN is using a subquery in the parentheses. Something like this... Code:
SELECT * FROM needles WHERE needle_id IN (SELECT needle_id FROM haystack WHERE length < 10) The quality of this board depends on the quality of the posts. The only way to guarantee thoughtful, informative discussion is to write thoughtful, informative posts. AppleNova is not a real-time chat forum. You have time to compose messages and edit them before and after posting. |
quote |
Member
Join Date: Jul 2004
|
wow, i think i have to read that a few times to understand it.
but, i tried both methods and neither worked for me. i've checked it and double checked it, but can't seem to make it work. currently i'm getting by with about 160 small database queries, but i wanted to bake them all into one or two queries instead. |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
Odd. It looks like MySQL does support the IN keyword:
http://dev.mysql.com/doc/refman/5.0/...ubqueries.html Going from 160 queries (!!) to one or two queries would indeed be a huge benefit in speed, readability, and maintainability, IMO. I'd carefully double-check that syntax to be sure there aren't any typos. The quality of this board depends on the quality of the posts. The only way to guarantee thoughtful, informative discussion is to write thoughtful, informative posts. AppleNova is not a real-time chat forum. You have time to compose messages and edit them before and after posting. |
quote |
Member
Join Date: Jul 2004
|
and as in most cases of unexplainable computer behavior... there is human oversight involved.
i was searching for values that didn't exist, on a table i hadn't finished. i noticed that IN does exist in MySQL too. thanks for the help brad! i'm on my way now i think. |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
PEBKAC!
Always glad to help. |
quote |
Member
Join Date: Jul 2004
|
what's PEBKAC??
|
quote |
Mac Mini Maniac
Join Date: Sep 2005
|
Problem Exists Between Keyboard And Chair - ancient computer slang.
|
quote |
Member
Join Date: Jul 2004
|
ok, i learned two things today then!
|
quote |
Posting Rules | Navigation |
|
Thread Tools | |
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
MySQL database disabled, how to "restart" it? | drewprops | Programmer's Nook | 3 | 2005-12-31 19:17 |
Christmas Santa/Jesus Question | CoolToddHunter | AppleOutsider | 32 | 2005-12-05 19:44 |
Basic PHP / mySQL question | Moogs | Programmer's Nook | 15 | 2005-11-19 16:56 |
MySQL 5.0 Install on OS X v.10.4.1- need help with socket error | DogMom | Genius Bar | 1 | 2005-07-11 17:00 |
Ram & Superdrive Question (Powerbook) | Fooboy | General Discussion | 2 | 2005-02-21 22:23 |