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 a.phone FROM xenia.customers a WHERE a.customer_id=l.customer_id) AS phone,(SELECT b.name FROM xenia.customers b WHERE b.customer_id=l.customer_id) AS NAME, (SELECT c.email FROM xenia.customers c WHERE c.customer_id=l.customer_id) AS email, (SELECT d.name FROM melv1n.country d WHERE d.id IN (SELECT e.country FROM xenia.customers e WHERE e.customer_id=l.customer_id)) AS country, (SELECT f.city FROM melv1n.cities f WHERE f.city_id IN (SELECT g.city 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 melv1n.country d where d.id 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


Subject
Views
Written By
Posted
how to use sub query column in where condition in mysql?
5401
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.