MySQL Forums
Forum List  »  Views

how to use sub query column in where condition in mysql?
Posted by: J Ashok Kumar
Date: December 11, 2013 08:12AM

Please check the below query. I am getting data from two databases and when i use the subquery result column in where condition, it is returning error in name column.

SELECT l.lead_id AS lead_id,l.customer_id AS customer_id,(SELECT FROM xenia.customers a WHERE a.customer_id=l.customer_id) AS phone,(SELECT FROM xenia.customers b WHERE b.customer_id=l.customer_id) AS NAME, (SELECT FROM xenia.customers c WHERE c.customer_id=l.customer_id) AS email, (SELECT FROM d WHERE IN (SELECT FROM xenia.customers e WHERE e.customer_id=l.customer_id)) AS country, (SELECT FROM melv1n.cities f WHERE f.city_id IN (SELECT FROM xenia.customers g WHERE g.customer_id=l.customer_id)) AS city FROM xenia.lead l where name like '%Johny%' LIMIT 0,10

even I tried to create view like below

create or replace view lead_view as select l.lead_id as lead_id,l.customer_id as customer_id,(select phone from xenia.customers a where a.customer_id=l.customer_id) as phone,(select name from xenia.customers b where b.customer_id=l.customer_id) as name, (select email from xenia.customers c where c.customer_id=l.customer_id) as email, (select name from d where in (select country from xenia.customers e where e.customer_id=l.customer_id)) as country, (select city from melv1n.cities f where f.city_id in (select city from xenia.customers g where g.customer_id=l.customer_id)) as city from xenia.lead l

but it is super slow.

Options: ReplyQuote

Written By
how to use sub query column in where condition in mysql?
December 11, 2013 08:12AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.