For some reason I receive the following error when using the IN clause for a subquery in MySQL but in the documentation it seems to show that I should be able to do this. First, here is the SQL that I'm trying to use:

SELECT * FROM IVPrescriptHdr WHERE PAT_NUMBER IN (SELECT PAT_NUMBER FROM IVPatientsOnly WHERE Phone LIKE '(727)%')

Here is the error message I'm receiving:
Error Code : 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 PAT_NUMBER FROM IVPatientsOnly WHERE Phone LIKE '(727)%'
(0 ms taken)

I can't see anything wrong with the syntax at all in my statement. Can someone please help me with this?

What version of mysql are you using? You might not be able to use IN.

I'm using 4.0.18 (latest stable version) ... The IN clause is supposed to be in this version from what I've seen in the documentation.

Ignore that... Your right quelphdad...

I have the latest stable version but it's 4.1 where they allowed sub queries... It wasn't the IN clause that was getting me it was the sub query... Doh!

Is that the exact query you are using? If it is, it should be
SELECT PAT_NUMBER FROM IVPatientsOnly WHERE Phone LIKE ('727%')

that isn't the case where his phone numbers show up as (727)-555-1212, your query won't match what he is looking for.

You are correct. I apologize for not looking at his syntax closer.