1

This query doesn't work but I hope it will suffice to make you understand what I need: 

SELECT p.ID, lat.l 
FROM hlp_posts AS p
WHERE p.ID 
IN (SELECT pm.post_id, pm.meta_value AS l FROM hlp_postmeta AS pm WHERE pm.meta_key = "hlp_latitude") lat

What I need is getting in the main SELECT a value (lat.l) selected in the subquery.
I can get it through a JOIN but I can't use it.

Ferex
  • 553
  • 6
  • 22
  • if you use the `IN` you are bound to return a single column in the subquery, which is the one used to evaluate the condition, this way is going to be impossible to fetch `meta_value `. Any specific reason why you can't use the `JOIN`? – Cavaz Aug 31 '17 at 21:59
  • I know it seems strange but JOINS in this case are behaving very slow. In the complete code I have like 15 JOINS. Since from a few tests subqueries are behaving better I am trying to refactor my code with them. – Ferex Aug 31 '17 at 22:04

1 Answers1

2

You can use JOIN instead, e.g.:

SELECT p.id, pm.meta_value
FROM hlp_posts AS p JOIN hlp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = "hlp_latitude";
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Are there any performance reason to use joins since in is old and should be avoid my question is specific if join will faster my query – Noob Aug 31 '17 at 21:52
  • @Noob you can refer to [this](https://stackoverflow.com/questions/2577174/join-vs-sub-query) SO answer. I'd say it depends on index rather than join vs sub query. – Darshan Mehta Aug 31 '17 at 21:55
  • Thanks this will help me since I want to proving my database skills specially on indexing. – Noob Aug 31 '17 at 21:59