[SOLVED] Frustrated with mySQL docs.

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
turbo2ltr
Forum Commoner
Posts: 29
Joined: Sun Jul 18, 2004 4:08 pm

[SOLVED] Frustrated with mySQL docs.

Post by turbo2ltr »

They aren't very clear to me.

I have a table with accessories and a table with products.
Then I have a third table that just has entries that crossreference what accessories will work with what products.

I let the user choose some other options held in the accessory table, but then I want them to be able to choose to get only the accessories that work with their products.

Seems simple enough, but I cant't get it to work right..

Code: Select all

SELECT  * FROM accessory, productlist WHERE accessory.acc_type =  "some type" AND ((productlist.accessory_id = accessory.accessory_id) AND (productlist.prod_id = "123" OR "111"))
It returns a bunch of rows even though there are only two accessories in the DB right now!



If someone could point me to a good tutorial on multiple table selects, that would greatly be appreciated. I understand the basics and I can usually hack my way though things, but this has me stumped. Whats weird is in another set of tables I did something similar and it seemed to work..so I don't get it.

Thanks,
Mike
Last edited by turbo2ltr on Sun Jul 18, 2004 7:01 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try

Code: Select all

SELECT  * FROM accessory, productlist WHERE accessory.acc_type =  "some type" AND productlist.accessory_id = accessory.accessory_id AND productlist.prod_id = IN("123","111")
turbo2ltr
Forum Commoner
Posts: 29
Joined: Sun Jul 18, 2004 4:08 pm

Post by turbo2ltr »

"Check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ( "123", "111" ) LIMIT 0, 30' at line 4"

What is this supposed to do? I tried to find IN on mssql.com to no avail..
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

http://dev.mysql.com/doc/mysql/en/Compa ... ators.html

Code: Select all

... IN('123','111')
...or...

Code: Select all

... IN(123,111)
...depending on field type. Either might work.
turbo2ltr
Forum Commoner
Posts: 29
Joined: Sun Jul 18, 2004 4:08 pm

Post by turbo2ltr »

AHH! No equal sign is needed between the column name and the IN.

Thanks!
-Mike
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

:oops: heh, I guess I was in a hurry. ;)
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

Code: Select all

SELECT  * FROM accessory INNER JOIN productlist ON accessory.accessory_id = productlist.accessory_id WHERE accessory.acc_type =  "some type"  AND (productlist.prod_id = "123" OR productlist.prod_id = "111")
Post Reply