syntax error when using "WHERE uid IN (subquery)"

pdaoust

I'm having a bizarre problem with MySQL; I can't see what I've done wrong. AFAIK, I'm following the MySQL manual perfectly.

First of all, here's the structure of the two tables:

accommodation
uid
label
etc.

amenity
uid
label
etc.

and a join table:

accommodationToAmenity
accommodationID
amenityID

Basically, I have a multiple-to-multiple relationship between 'accommodation' (a list of houses available for rent) and 'amenity' (a list of amenities and features such as hot tub, fireplace, patio, pet-friendly, etc.)

So, being rather ignorant of the 'JOIN' keyword in MySQL, I decided to use this query:

SELECT * FROM amenity WHERE uid IN (
     SELECT amenityID FROM accommodationToAmenity WHERE accommodationID =1
)

This should grab the list of amenityIDs from the join table (for accommodationID = 1) and use that as a list for comparing against the primary key (uid) in the amenity table, right?

Wrong! it tells me that there's a syntax error near the subquery. Here's the exact output:

#1064 - You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server
version for the right syntax to use near 
'SELECT amenityID
FROM accommodationToAmenity
WHERE accommodatio

any ideas? am I completely off-course here?

onion2k

Err.. subselects aren't available in MySQL until version 4.1 ... What version are you using? I really wouldn't recommend using 4.1 in a production environment yet as its only in the Alpha stage at the moment.

pdaoust

ha ha, thanks! I wasn't aware that subqueries were a new thing. My test setup on my home computer apparently uses MySQL 4.0.18, and 4.1 isn't even available for my distro (Gentoo) yet.

Guess I'd better teach myself how to use the JOIN statement then...

eventually I want to use Firebird SQL on my testbed and my server, but right now we're using virtual hosting and only have MySQL and PostgreSQL as choices.

chriskl

Switch to using PostgreSQL then - it has full support for subselects, and more.

MySQL has an annoying habit of putting in its manual documentation for features that they don't plan to release for several years!

pdaoust

oh wait, sorry, I checked and we only have MySQL. I can't wait until we have our own server up and running; then I can throw whatever I want on it...

chriskl (or anybody), would you have any experience with Firebird? How does it compare to PostgreSQL, in terms of performance and features?