User Name
Password
AppleNova Forums » Programmer's Nook »

MySQL syntax question


Register Members List Calendar Search FAQ Posting Guidelines
MySQL syntax question
Thread Tools
nassau
Member
 
Join Date: Jul 2004
 
2006-11-06, 13:42

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
Brad
Selfish Heathen
 
Join Date: May 2004
Location: Zone of Pain
 
2006-11-06, 14:35

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
nassau
Member
 
Join Date: Jul 2004
 
2006-11-06, 16:58

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
Brad
Selfish Heathen
 
Join Date: May 2004
Location: Zone of Pain
 
2006-11-06, 17:11

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
nassau
Member
 
Join Date: Jul 2004
 
2006-11-07, 07:13

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
Brad
Selfish Heathen
 
Join Date: May 2004
Location: Zone of Pain
 
2006-11-07, 07:30

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
nassau
Member
 
Join Date: Jul 2004
 
2006-11-07, 07:31

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
Brad
Selfish Heathen
 
Join Date: May 2004
Location: Zone of Pain
 
2006-11-07, 09:08

PEBKAC!



Always glad to help.
  quote
nassau
Member
 
Join Date: Jul 2004
 
2006-11-07, 12:09

what's PEBKAC??

  quote
Yonzie
Mac Mini Maniac
 
Join Date: Sep 2005
 
2006-11-07, 12:25

Problem Exists Between Keyboard And Chair - ancient computer slang.
  quote
nassau
Member
 
Join Date: Jul 2004
 
2006-11-07, 13:45

ok, i learned two things today then!
  quote
Posting Rules Navigation
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Post Reply

Forum Jump
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


« Previous Thread | Next Thread »

All times are GMT -5. The time now is 15:06.


Powered by vBulletin®
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004 - 2024, AppleNova