SQL query to display rows that match multiple columns or join multiple rows together based on city-country match

Question

I have a table that looks like 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

I need to display all the entries that match Country > City. Or order all the _aciudad entries by country; something like:

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 have no idea how to do this; I guess with an INNER JOIN?

What's the link between miami and Usa? The post_id? But post id 43 has at least 6 records across different _aciudad. Actually, I struggle to see how you got from 238/43/Miami to the result — RichardTheKiwi
– RichardTheKiwi, Commented Mar 28, 2011 at 22:18

rkg · Accepted Answer · 2011-03-28 22:13:13Z

1

   SELECT t1.meta_id, 
           t1.post_id, 
           t1.meta_key, 
           t1.meta_value, 
           t2.meta_key, 
           t2.meta_value 
    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

answered Mar 28, 2011 at 22:13

rkg

5,6849 gold badges39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Dinescu · Accepted Answer · 2011-03-28 22:11:55Z

1

I'm not sure if this is what you're looking for, or if the JOIN ON field is correct because it's not very clear in your question but what about this query?

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

answered Mar 28, 2011 at 22:11

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

Comments

Quassnoi · Accepted Answer · 2011-03-28 22:11:37Z

0

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'
ORDER BY
        mp.meta_value, mc.meta_value

answered Mar 28, 2011 at 22:11

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

1 Comment

Quassnoi Over a year ago

@Richard: maybe, and maybe he wants all cities/countries listed.

Ben · Accepted Answer · 2011-03-28 22:13:03Z

0

Try a self-join. To make it work you have to use aliases for the table, so you can refer to it twice.

Something like this:

select

  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"

answered Mar 28, 2011 at 22:13

Ben

35.8k6 gold badges81 silver badges117 bronze badges

1 Comment

Ben Over a year ago

The data posted is nonsense, so I guessed. I really think he just needs to be told how to do a self-join, and he will work out the rest for himself.

Collectives™ on Stack Overflow

SQL query to display rows that match multiple columns or join multiple rows together based on city-country match

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related