mysql query

[abhilashdas]

Can anybody tell me whts wrong with this query SELECT * FROM `tbl_stock` WHERE code in (select code FROM tbl_stock)
never mind the logic!! My concern is that it shows querry error.

[Lord Orwell]

it looks to me like this part needs something after it, like an = 0 or = "whaterver"

[si_the_geek]

The syntax is valid, the issue is likely to be the field or table names being invalid (they aren't in the reserved words list, but they may still be conflicting), or that MySQL is confused about what you are doing.

For the latter issue, it would probably be better to use an alias in the subquery, eg:

(select t2.code FROM tbl_stock t2)

[kxcntry99]

Does MySQL even support the "code in" function? I have never tried it.

[si_the_geek]

The IN keyword is standard SQL - I would be extremely surprised if any of the popular DBMSs don't support it.

Typically you will use IN with a list of values. In the help for SQL Server (Books Online) they show this example of IN:

...
WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant')

..and the equivalent without IN:

...
WHERE e.Title = 'Design Engineer' 
   OR e.Title = 'Tool Designer' 
   OR e.Title = 'Marketing Assistant'

The fact that you can use a query to get the values (instead of a fixed list like in the example), means that you can perform much more powerful queries, with far less effort (and much more speed) than writing code to do the same thing.