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?