My Sql AND Relation for multiple rows

How to write sql query for set of key value pairs using AND operation

Ex:

 id   proc_id     code     value
 1    1           height   ""
 2    1           weight   120
 3    1           chin     2
 4    1           lower    ""
 5    1           upper    10

I have values like this : {weight=120, lower=1, height=""}

How can i apply AND relation to match exact records

output : return records - [2开发者_运维知识库, 1] - # only weight and height matched .

Since the individual values are in separate rows, you'd have to use an OR query:

select id, proc_id, code, value
FROM yourtable
WHERE (code='HEIGHT' and value = '120') OR
    (code='lower' AND value = '1') OR
    (code='height' AND value= '')

There are three basic ways.

One is to join as many times as needed:

select n.node_id
from node n
join meta m1 on m1.node_id = n.node_id
            and m1.code = 'weight' and m1.value = '120'
join meta m2 on m2.node_id = n.node_id
            and m2.code = 'lower' and m2.value = '1'
join meta m3 on m3.node_id = n.node_id
            and m3.code = 'height' and m3.value = ''

The other is to group by and count:

select n.node_id
from node n
join meta m on m.node_id = n.node_id
where m.code = 'weight' and m.value = '120' or
      m.code = 'lower' and m.value = '1' or
      m.code = 'height' and m.value = ''
group by n.node_id
having count(*) >= 3

The last is to avoid using an EAV where inappropriate and to normalize your data properly.

Not sure I understand completely, but how about this:

SELECT * FROM your_table
WHERE weight = 120 AND lower = 1 AND height = ''