How can I write an SQL query with multiple `WHERE` statements for different conditions?

General Discussions
1

I have a MySQL database with two tables:

items

ID, item_name, item_description

items_meta

meta_id, item_id, meta_key, meta_value

I want to filter items based on multiple meta conditions, like lat between 55 and 65, and long between 20 and 30. My initial query combining conditions didn’t work as expected.

How can I correctly write a query with multiple where statements SQL* so that both conditions are applied simultaneously?

Here’s an example of a working solution I found:

SELECT SQL_CALC_FOUND_ROWS items.* 
FROM items
INNER JOIN items_meta ON (items.ID = items_meta.post_id)
INNER JOIN items_meta AS m1 ON (items.ID = m1.post_id)
WHERE items.post_type = 'post'
  AND items.post_status = 'publish'
  AND ((items_meta.meta_key = 'lat' AND CAST(items_meta.meta_value AS SIGNED) BETWEEN '55' AND '65')
       AND (m1.meta_key = 'long' AND CAST(m1.meta_value AS SIGNED) BETWEEN '20' AND '30'))
GROUP BY items.ID
ORDER BY items.date DESC;
2

I’ve run into this exact issue :sweat_smile:. When you have different meta keys in the same table, you can’t just combine conditions on one alias, they conflict. Using multiple JOINs solves it nicely:

SELECT items.*
FROM items
INNER JOIN items_meta AS lat_meta ON items.ID = lat_meta.item_id
INNER JOIN items_meta AS long_meta ON items.ID = long_meta.item_id
WHERE lat_meta.meta_key = 'lat'
  AND CAST(lat_meta.meta_value AS SIGNED) BETWEEN 55 AND 65
  AND long_meta.meta_key = 'long'
  AND CAST(long_meta.meta_value AS SIGNED) BETWEEN 20 AND 30;

Each JOIN represents a different meta condition.

This ensures both conditions are applied simultaneously.

Works great for filtering items by multiple custom fields.

3

Another approach I like for flexible filtering is conditional aggregation with GROUP BY:

SELECT items.*
FROM items
INNER JOIN items_meta ON items.ID = items_meta.item_id
WHERE (meta_key = 'lat' AND CAST(meta_value AS SIGNED) BETWEEN 55 AND 65)
   OR (meta_key = 'long' AND CAST(meta_value AS SIGNED) BETWEEN 20 AND 30)
GROUP BY items.ID
HAVING COUNT(DISTINCT meta_key) = 2;  -- ensures both conditions are met

Here, you collect all relevant meta rows and then ensure both exist with HAVING.

Handy when the number of conditions is dynamic.

I use this in situations where adding more JOINs feels messy.

4

Sometimes I prefer subqueries for readability:

SELECT *
FROM items
WHERE ID IN (
    SELECT item_id
    FROM items_meta
    WHERE meta_key = 'lat' AND CAST(meta_value AS SIGNED) BETWEEN 55 AND 65
)
AND ID IN (
    SELECT item_id
    FROM items_meta
    WHERE meta_key = 'long' AND CAST(meta_value AS SIGNED) BETWEEN 20 AND 30
);

Each condition is isolated in its own subquery.

Easy to maintain when filtering by many meta keys.

I usually pick this approach if performance is okay and readability matters more.