MySQL Forums
Forum List  »  Views

Re: how to use sub query column in where condition in mysql?
Posted by: Peter Brawley
Date: December 11, 2013 12:53PM

Forget about Views until the query is correct, debugged and optimised.

When you find yourself writing multiple correlated subquery joins to the same table, stop and think: one FROM clause JOIN to that table will perform better and will be easier to verify, debug and maintain.

And IN(SELECT...) should most always be rewritten as a JOIN.

I think those changes would give you something like this rather simpler query:

SELECT 
  l.lead_id, l.customer_id,
  a.phone, a.name, a.email
  d.name as country,
  f.city as city
FROM xenia.lead      l 
JOIN xenia.customers a ON a.customer_id=l.customer_id
JOIN melv1n.country  d ON d.id=a.country 
JOIN melv1n.cities   f ON f.city=a.city
WHERE name like '%Johny%' 

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to use sub query column in where condition in mysql?
1534
December 11, 2013 12:53PM


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.