MySQL selects a query in where where

Question

Can this request be fixed? it shows some syntax error.

SELECT * WHERE `id` IN (SELECT DISTINCT unit_trust_managing_company_id FROM ut_funds ORDER BY `company_name`) 

SELECT DISTINCT unit_trust_managing_company_id FROM ut_funds ORDER BY company_name` query works correctly.

Answer 1

You need a from clause before where :

 SELECT * FROM <some table here> WHERE `id` IN (SELECT unit_trust_managing_company_id FROM ut_funds) 

In addition, distinct and order by not needed for the in operator.

Answer 2

In most cases (all?), You can rewrite the request without using the request in the where clause and get much better performance. Try something like:

  SELECT DISTINCT t.* FROM some_table t, ut_funds u WHERE t.id=u.unit_trust_managing_company_id