Page 1 of 1

[SOLVED] Frustrated with mySQL docs.

Posted: Sun Jul 18, 2004 6:30 pm
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

Posted: Sun Jul 18, 2004 6:42 pm
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")

Posted: Sun Jul 18, 2004 6:51 pm
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..

Posted: Sun Jul 18, 2004 6:55 pm
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.

Posted: Sun Jul 18, 2004 7:00 pm
by turbo2ltr
AHH! No equal sign is needed between the column name and the IN.

Thanks!
-Mike

Posted: Sun Jul 18, 2004 9:05 pm
by feyd
:oops: heh, I guess I was in a hurry. ;)

Posted: Mon Jul 19, 2004 8:12 am
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")