CODEWITHSUNDEEP
mysqlsql
shaithana
shaithana

Reputation: 2490

Select multiple rows that correspond to a single condition

I have a table where POST_ID contains an ID (that identifies a post), META_KEY contains the type of data (“latitude” or “longitude”) and META_VALUE contains the value for the key.

POST_ID META_KEY  META_VALUE
   1879 latitude   14.846329
   1879 longitude  42.947395
   1849 latitude   18.543265
   1849 longitude  41.849382
   1754 latitude   14.846329
   1754 longitude  41.849382

In simple English, I need to know the ID of the post where latitude is 14.846329 and longitude is 42.947395 (in this case 1879)

I am totally confused at the moment: what’s the MySQL query to get the correct result?

Thanks.

Upvotes: 1

Views: 41

Answers (2)

juergen d
juergen d

Reputation: 204924

Group by the post_id and take only those having these values

select post_id
from your_table
group by post_id
having sum(meta_key = 'latitude' and meta_value = '14.846329') > 0
   and sum(meta_key = 'longitude' and meta_value = '42.947395') > 0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One method uses a join:

select t.post_id
from t tlat join
     t tlong
     on tlat.post_id = tlong.post_id and
        tlat.meta_key = 'latitude' and tlat.meta_value = '14.846329'
        tlong.meta_key = 'longitude' and long.meta_value = '42.947395';

Note: This uses single quotes, assuming that the meta_value is a string.

Upvotes: 0

Related Questions