Multiple meta key and meta value search

Question

I am working on client project and stumped upon multiple meta_key, meta value search.

My db structure is like that

clinics                   clinics_meta
=======                   =============
id   name                id fk_clinic_id   meta_key   meta_value
1    dental               1   1             city       london
2    heart                2   1             country    england
                          3   2             city       manchester
                          4   2             country    london

I want to get clinic with city = london and country = england

I tried like this

SELECT distinct(clinics.id) FROM clinics, clinic_meta
WHERE clinics.id = clinic_meta.fkClinicId
AND  (clinic_meta.metaKey = 'clinicCountry' AND clinic_meta.metaValue like '%england%')
AND  (clinic_meta.metaKey = 'clinicCity' AND clinic_meta.metaValue like '%london%')

Please dont conside syntax error but the logic.

this code does not return clinic name/id,

but instead of using two filter city,country

If I use only one filter it does return clinic name.

Any help will be appreciated.

Thanks.

RJJ RJJ · Accepted Answer · 2016-06-11T08:02:21

    select cl.* from clinics cl
     inner join 
     clinics_meta cm1 
     on cl.id=cm.clinic_id
     inner join 
     clinics_meta cm2
     on cm1.clinic_id=cm2.clinic_id
     where cm1.meta_key='Country' and cm1.meta_value='England' 
     and cm1.meta_key='City' and cm2.meta_value='London'
    group by t1.id;

Multiple meta key and meta value search

1 Answers