"WHERE ... IN" Usage problem

F

fatih.koz

Perch
Hi ,

I'm trying to use a basic syntax in one of my pages which uses MySQL data .

Code: 
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 ;

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 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

Any help or idea will be great .

Thanks
 
If subqueries don't work then you can often achieve the same thing with a join.

Code: 
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.

Cheers
Ross
 
Hi ,

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 :(

Anyway , thanks for the answers and suggestions ;)
 
fatih.koz said:
Sadly I need all the fields from reports
Click to expand...

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.)

Tim
 
You must log in or register to reply here.
Back
Top