How to get distinct ids from a pivot like MySQL table according to multiple row values?

Question

I have a MySQL table like the below one named attribute_master,

Now I want to get all the ref_id having f_name = "Sam" and l_name = "Border".

How can I do that using a MySQL query?

Answer 1

You can use aggregation:

select ref_id
from mytable
where attr_name in ('f_name', 'l_name')
group by ref_id
having
    max(attr_name = 'f_name' and attr_value = 'Sam') = 1
    and max(attr_name = 'l_name' and attr_value = 'Border') = 1

Here is another way to express it (assuming no duplicates (ref_id, attr_name)):

select ref_id
from mytable
where (attr_name, attr_value) in (('f_name', 'Sam'), ('l_name', 'Boder'))
group by ref_id
having count(*) = 2