CODEWITHSUNDEEP
mysql
HWD
HWD

Reputation: 1629

MYSQL Multiple Key, Value, Between Query

I'm trying to write a query like this, but just can't seem to work it out:

WHERE  
    (
        meta_key = 'key' 
        AND 
        meta_value = 'value' BETWEEN min_value AND max_value
    )
    AND 
    (
        meta_key = 'different_key' 
        AND 
        meta_value = 'different_value' BETWEEN different_min_value AND different_max_value
    )

In other words, I want to check if the value of a key is between two numbers AND the value of another key is between two other numbers.

When I use the OR operator, it returns two of the same items, when I just want one.

Help is greatly appreciated!

Upvotes: 0

Views: 387

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

You need to Join the table twice to test 2 different conditions on 2 rows. This is common (to have a lot of Joins) in EAV designs:

FROM
    tableX AS a
  JOIN
    tableX AS b
      ON  b.identifyingColumn = a.identifyingColumn

WHERE  
    (
        a.meta_key = 'key' 
        AND 
        a.meta_value BETWEEN min_value 
                         AND max_value
    )
    AND 
    (
        b.meta_key = 'different_key' 
        AND 
        b.meta_value BETWEEN different_min_value 
                         AND different_max_value
    )

Upvotes: 1

user319198
user319198

Reputation:

It can't be with AND. because a row can't have different values for same column.

I think you should go for UNION query or self join.

Upvotes: 0

Related Questions