phpmyadmin Question posted in
The official documentation can be found here.

How to add multiple AND's and BETWEEN's correctly in a SQL query? – Phpmyadmin

I’m trying to build a filter function with several dependencys but when i add an AND to the SQL query, it seems like no results were found but if i’m searching with the queries before and after the AND, the right results are found.

I try to build a filter function where users can filter between ranges for Example: price: between 100$ and 200$ and squarefeet between 30 and 80.

My approach looks like this:

SELECT * FROM 'tableXY' WHERE value BETWEEN 100 AND 200 AND key ='price' 
AND value BETWEEN 2 AND 5 AND key = 'rooms'
AND value BETWEEN 40 AND 80 AND key = 'sqft'......and so on.

The table contains the columns id, foreign_id, key and value. The keys in the rows are price, sqft, rooms, addres etc.
If i filter for a certain price range between price x and price y and rooms between n rooms and m rooms, it should only return results where ervery filter which was set applies to.

No results are found with my approach and if i use an OR instead of an AND, it also shows Results where the other filters dosen’t aplly to.

Tags:

5

Answers


  1. 0

    I think you want aggregation, because the values are in different rows:

    SELECT something
FROM tableXY
GROUP BY something 
HAVING SUM( value BETWEEN 100 AND 200 AND key ='price' ) > 0 AND
       SUM( value BETWEEN 2 AND 5 AND key = 'rooms' ) > 0 AND
       SUM( value BETWEEN 40 AND 80 AND key = 'sqft' ) > 0 AND
       ......and so on.
    Login or Signup to reply.
  2. 0

    Seems like you need a logical or operation between each pair of conditions on value and key:

    SELECT *
FROM   tableXY
WHERE  (value BETWEEN 100 AND 200 AND key ='price' ) OR 
       (value BETWEEN   2 AND   5 AND key = 'rooms') OR
       (value BETWEEN  40 AND  80 AND key = 'sqft' ) -- etc...

    Note: Since and has a higher precedence than or the parenthesis aren’t strictly needed, but they do make the query easier to read.

    Login or Signup to reply.
  3. 0

    Solution 1: Use or between conditons for different key values.

    SELECT * FROM 'tableXY' WHERE (value BETWEEN 100 AND 200 AND key ='price') 
OR (value BETWEEN 2 AND 5 AND key = 'rooms')
OR (value BETWEEN 40 AND 80 AND key = 'sqft')......and so on.

    Solution 2: You need to use case when

    SELECT * FROM 'tableXY' 
WHERE 1 = (
 case 
  when key ='price' and value BETWEEN 100 AND 200 
     then 1
  when key ='rooms' and value BETWEEN 2 AND 5
     then 1
.....
  else 0
 end
)

    Here is why your initial clause didn’t find records. Your initial approach using AND between condition sets basically selects
    WHERE ... value BETWEEN 2 AND 5 AND... value BETWEEN 40 AND 80
    Obviously no result

    Login or Signup to reply.
  4. 0

    If you used union in a similar way to: 

    QueryWithRoomCostFilter UNION  QueryWithRoomSizeFilter

    Then what happens is it gets the set of tuples that match with your cost filter 100$-200$ and adds them to the result set, then it will evaluate the second query with a filter for room size 40-80 sqft and add those tuples to the result set, effectively combining (taking the union) of both results. In this case, you could see that union is is working as if you had an OR between your conditions.

    I don’t use value and between often, but maybe try something along these lines and let us know if it still doesn’t work.

    SELECT * 
FROM table_name
WHERE (price BETWEEN 100 AND 200)
AND (rooms BETWEEN 2 AND 5)
AND (sqft BETWEEN 40 AND 80)
    Login or Signup to reply.
  5. 0

    With key-value pair tables as your post indicates, your data is in long format where different unit type of data values and indicators reside in two columns: key and value. Hence, each AND condition attempts to select one condition pair of key and value logic and cannot meet all at once unless values like price, rooms, and sqft are stored in their specific columns in a wide format.

    Long Term Solution

    There is a long-debated question on using the entity-attribute value model such as this SO post in relational databases. The long-term solution to effectively filter mutually inclusive conditions is to redesign your table to wide format where values are in their own columns and where querying is much easier with AND conditions.

    CREATE TABLE tableXY (
    `price` DOUBLE,
    `room` INT
    `sqlft` INT,
    ... OTHER indicators ...
);

SELECT * 
FROM tableXY 
WHERE `price` BETWEEN 100 AND 200
  AND `rooms` BETWEEN 2 AND 5
  AND `sqft` BETWEEN 40 AND 80

    Short Term Solution

    But the short-term solution for existing long format is to run conditional aggregations to create “pseudo columns” of values: price, rooms, sqft. Add GROUP BY for any static attributes (names, locations, time, etc.).

    CREATE TABLE tableXY (
    `key` VARCHAR(10),
    `value` INT
);

SELECT sub.* 
FROM 
   (SELECT CASE WHEN `key` = 'name' THEN `key` ELSE NULL END AS `name`,
           SUM(CASE 
                   WHEN `key` = 'price'
                   THEN `value`
                   ELSE NULL
               END) AS price,
           SUM(CASE 
                   WHEN `key` = 'rooms' 
                   THEN `value`
                   ELSE NULL
               END) AS rooms,
           SUM(CASE 
                   WHEN `key` = 'sqlft' 
                   THEN `value
                   ELSE NULL
               END) AS sqft,

           -- ...other values...

     FROM tableXY

     GROUP BY CASE WHEN `key` = 'name' THEN `key` ELSE NULL END
  ) AS sub

WHERE sub.price BETWEEN 100 AND 200
  AND sub.rooms BETWEEN 2 AND 5
  AND sub.sqft BETWEEN 40 AND 80
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search