I've got a table that appears such as this:

meta_id post_id     meta_key    meta_value
271     4   _aciudad             New york
270     4   _apais               USA
267     10  _aservicio           Alojamiento

261     43  _apais               USA
238     43  _aciudad             Chicago
262     43  _aservicio           Alojamiento
261     43  _apais               USA
238     43  _aciudad             Miami
262     43  _aservicio           Alojamiento

What i have to do, would be to display all of the registers that suits Country > City. Or order all of the _aciudad registers based on country, something similar to:

meta_id post_id  meta_key   meta_value       meta_key    meta_value 
235      42      _aciudad        New York     _apais       USA
236      56      _aciudad        Chicago      _apais       USA
237      57      _aciudad        Miami        _apais       USA
238      58      _aciudad        Sidney       _apais      Australia
238      59      _aciudad        Melbourne    _apais      Australia

I've no clue how to get this done, i suppose having a INNER JOIN? a double choose? Help me!!

I am unsure if this sounds like what you are searching for, or maybe the JOIN ON area is correct since it is not so obvious inside your question but how about this question?

SELECT City.meta_id
     , City.post_id
     , City.meta_key
     , City.meta_value
     , Country.meta_key
     , Country.meta_value
  FROM yourTable City
INNER JOIN yourTable Country ON City.post_id = Country.post_id
WHERE City.meta_key = '_aciudad' AND Country.meta_key = '_apais'
ORDER BY Country.meta_value, City.meta_value
   SELECT t1.meta_id, 
    FROM table t1, table t2  
    WHERE t1.post_id = t2.post_id 
    AND t1.meta_key = '_apais'
    AND t2.meta_key = '_aciudad'
    ORDER BY t1.meta_key
SELECT  mc.*, mp.meta_key, mp.meta_value
FROM    meta mc
JOIN    meta mp
ON      mp.post_id = mc.post_id
        AND mp.meta_key = '_apais'
WHERE   mc.meta_key = '_aciudad'
        mp.meta_value, mc.meta_value

Consider using a self-join. To really make it work make use of aliases for that table, so that you can make reference to it two times.

Something similar to this:


  t1.meta_id, t1.post_id,
  t1.meta_key, t1.meta_value, 
  t2.meta_key, t2.meta_value

from TableName t1
  inner join TableName t2
  on t1.meta_id = t2.meta_id and t1.post_id = t2.post_id
    and t1.meta_key = "_aciuidad" and t2.meta_key = "_apais"