I’m trying to use a basic syntax in one of my pages which uses MySQL data .
SELECT * FROM reports WHERE staff_id IN ( SELECT staff_id FROM staff WHERE department_id = 1 )
Logic is simple as you can see , I want to see the reports of staff whom works for department 1 .
Both staff and reports table have the field staff_id , so we do not have any type or table config errors BUT MySQL returns this error ;
#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 staff_id FROM staff WHERE department_id = 1 )
LIMIT 0, 30' at
I checked the MySQL Documentation and saw that it supports WHERE … IN clause , also they used a very simple and similiar code as an example on their pages .
What can be the problem ? Is there any chances that our (JodoHost’s) MySQL servers do not support this kind of basic syntax .
BTW I’m using Jodo MySQL 5 for my database , the one with ip 64.1XX.XXX.X50
If subqueries don’t work then you can often achieve the same thing with a join.
SELECT * FROM reports INNER JOIN staff ON reports.staff_id = staff.staff_id
WHERE staff.department_id = 1
The * is usually not a good idea for performance and I’m not quite sure what it gives you with a join (all fields from both tables perhaps?). Its better to SELECT reports.column1, reports.column2 type of thing and get only what you need.
Sadly I need all the fields from reports ( expect one or two ) but the second table will be used for only being selective about staff which will recuce the selection and server load very well .
I’m sad to hear about the MySQL’s version here . I’m not going to ask for an update but at least I think we deserve better here at Jodo .
As you know MySQL moved in Ver5 and leave behind the 4.1 , but here we are using 4.0
It’s still better to name all the fields you need rather than use *. You’ll get a bit better performance, be sure of what you’re getting, and you can use field aliases (select reports.column1 as rc1, reports.column2 as rc2, etc.)
