CODEWITHSUNDEEP
mysql
user1117774
user1117774

Reputation: 911

sql query with multiple where statements

I am having a rather, for me, complicated mysql query on which I am totally stuck and cannot find any answer for online.

Here's my query:

SELECT
items.*
FROM
items
INNER JOIN
items_meta_data
WHERE
(
        (meta_key = 'lat' AND meta_value >= '55')
    OR
        (meta_key = 'lat' AND meta_value <= '65')
)
AND
(
        (meta_key = 'long' AND meta_value >= '20')
    OR
        (meta_key = 'long' AND meta_value <= '30')
)
GROUP BY
item_id

Of course I have tested the query with only 1 statement and that works fine. So, if I only pass the long or lat part, then I get results. Only when I try to stitch them together, I get different results.

Thanks for the help in advance!

The table structure is as follows:

Table items:
ID
item_name
item_description

Table meta:
meta_id
item_id
meta_key
meta_value

Solution

To whoever is interested I finally managed to tackle this problem. Thank you all for your help and insides.

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
    1=1
    AND
    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

Upvotes: 89

Views: 649469

Answers (4)

codeling
codeling

Reputation: 11369

You need to consider that GROUP BY happens after the WHERE clause conditions have been evaluated. And the WHERE clause always considers only one row, meaning that in your query, the meta_key conditions will always prevent any records from being selected, since one column cannot have multiple values for one row.

The meta_value checks given in your question also are redundant - If a value is allowed to be both smaller and greater than a given value, then its actual value doesn't matter at all - the check can be omitted.

But this hints at your problem - you actually want to check two separate rows of your meta table; according to one of your comments you want to check for places less than a certain distance from a given location. To get correct distances, you'd actually have to use some kind of proper distance function (see e.g. this question for details). But this SQL should give you an idea how to start, it shows how to join the item element with the two entries in the meta table that are needed (one with meta_key = 'lat' and one with meta_key = 'long'):

SELECT items.* FROM items i, meta m1, meta m2
    WHERE i.item_id = m1.item_id and i.item_id = m2.item_id
    AND m1.meta_key = 'lat' AND m1.meta_value >= 55 AND m1.meta_value <= 65
    AND m2.meta_key = 'lng' AND m2.meta_value >= 20 AND m2.meta_value <= 30

Upvotes: 55

David
David

Reputation: 218818

What is meta_key? Strip out all of the meta_value conditionals, reduce, and you end up with this:

SELECT
*
FROM
meta_data
WHERE
(
        (meta_key = 'lat')
)
AND
(
        (meta_key = 'long')
)
GROUP BY
item_id

Since meta_key can never simultaneously equal two different values, no results will be returned.

Based on comments throughout this question and answers so far, it sounds like you're looking for something more along the lines of this:

SELECT
*
FROM
meta_data
WHERE
(
    (meta_key = 'lat')
    AND
    (
        (meta_value >= '60.23457047672217')
        OR
        (meta_value <= '60.23457047672217')
    )
)
OR
(
    (meta_key = 'long')
    AND
    (
        (meta_value >= '24.879140853881836')
        OR
        (meta_value <= '24.879140853881836')
    )
)
GROUP BY
item_id

Note the OR between the top-level conditionals. This is because you want records which are lat or long, since no single record will ever be lat and long.

I'm still not sure what you're trying to accomplish by the inner conditionals. Any non-null value will match those numbers. So maybe you can elaborate on what you're trying to do there. I'm also not sure about the purpose of the GROUP BY clause, but that might be outside the context of this question entirely.

Upvotes: 4

Wes Crow
Wes Crow

Reputation: 2967

Can we see the structure of your table? If I am understanding this, then the assumption made by the query is that a record can be only meta_key - 'lat' or meta_key = 'long' not both because each row only has one meta_key column and can only contain 1 corresponding value, not 2. That would explain why you don't get results when you connect the with an AND; it's impossible.

Upvotes: 1

gbn
gbn

Reputation: 432210

This..

(
        (meta_key = 'lat' AND meta_value >= '60.23457047672217')
    OR
        (meta_key = 'lat' AND meta_value <= '60.23457047672217')
)

is the same as

(
        (meta_key = 'lat')
)

Adding it all together (the same applies to the long filter) you have this impossible WHERE clause which will give no rows because meta_key cannot be 2 values in one row

WHERE
    (meta_key = 'lat' AND meta_key = 'long' )

You need to review your operators to make sure you get the correct logic

Upvotes: 11

Related Questions