0

I have 2 table called Classifieds and Classifieds_meta below is the screenshot of classifieds_meta table

CLICK TO SEE THE SCREENSHOT

im writing a search filter function with muliple where clause like 

SELECT *,classifieds.id as id 
FROM classifieds,classifieds_meta 
WHERE (category=2 OR category=3 OR category=4 OR category=5) 
AND ((meta_key='vehicle_make' and meta_value=3 OR meta_key='vehicle_make' and meta_value=4)
AND meta_key='vehicle_mileage' and meta_value=9 OR meta_key='vehicle_mileage' and meta_value=10 OR meta_key='vehicle_mileage' and meta_value=11 ) 
AND classifieds.id=classifieds_meta.classifieds_id 
GROUP BY classifieds.id

But the above sql statement IGNORES vehicle_make, meta_value and meta_key field condition, and displays incorrect result, what i want to achieve exactly is i want to get vehicles where category is in (2,3,4 or 5) and meta_key is vehicle_make and meta_value is 3 or 4 and meta_key is vehicle_mileage and meta_value is 9,10 or 11.

can someone please help me to form better SQL statement to get right result

Improve this question
2
  • A screen dump doesn't allow for easy setup of a test case. Could you add some sample data and a desired result as text directly to your question? Commented Jun 14, 2014 at 15:28
  • Possible duplicate stackoverflow.com/q/24123489/1741542 The database design seems similar meta_key/value vs propertyname/value. meta_key can't be vehicle_make and vehicle_mileage at the same time. Commented Jun 14, 2014 at 15:39

3 Answers 3

Reset to default
2

I suppose you are looking for vehicles where category is 2,3,4 or 5 and (meta_key is vehicle_make and (meta_value is 3 or 4)) or (meta_key is vehicle_mileage and (meta_value is 9,10 or 11)).

In this case the query should be:

SELECT *,classifieds.id as id
FROM classifieds,classifieds_meta 
WHERE (category=2 OR category=3 OR category=4 OR category=5) 
AND  ((meta_key='vehicle_make' AND ( meta_value=2 OR meta_value=4)) OR (meta_key='vehicle_mileage' AND (meta_value=9 OR meta_value=10 OR meta_value=11)))

Use parenthesis to make your statement understandable and finally use the same parenthesis in your SQL query.

EDIT:

If you want to use IN the query would be:

SELECT *,classifieds.id as id
FROM classifieds,classifieds_meta 
WHERE category IN (2, 3, 4, 5) 
AND  ((meta_key='vehicle_make' AND  meta_value IN (2,4)) OR (meta_key='vehicle_mileage' AND meta_value IN (9, 10, 11)))
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i want to fullfill both the condition vehicle_make and vehicle_mileage but you have used OR condition, if i replace it with AND it returns empty result even though i have rows matching both the condition
1

I think you are looking for a having clause instead of a where. You are trying to look for conditions across multiple rows:

SELECT *, c.id as id 
FROM classifieds c join
     classifieds_meta  cm
     on c.id = cm.classifieds_id 
GROUP BY c.id
HAVING sum(meta_key = 'vehicle_make' and meta_value in (3, 4)) > 0 and
       sum(meta_key = 'vehicle_mileage' and meta_value in (9, 10, 11)) > 0 and
       sum(category in (2, 3, 4, 5)) > 0

The > 0 is saying that at least one row matches each condition.

Note that I also fixed the join to be use explicit join syntax.

Improve this answer

10 Comments

Thanks for helping, but where is the condition for meta_key = 'vehicle_make' and meta_value in (2,3)? anyways your sql returns empty results
Thanks alot, but still your sql statement returns empty set even if there is rows matching the condition
Are you looking for all conditions being true or for any of them? The above returns ids where all are true. If you want any to be true, change the AND to OR.
When looking at the image, I suspect meta_value is of type char and some of the values are CSV.
@OlafDietsche . . . That is a good point, but MySQL should handle the comparisons correctly, unless there are unusual characters in the value. Using bare integers is how the OP originally framed the query;.
|
0

This is essentially what you have (excluding table connection and category filter):

((meta_key='vehicle_make' and meta_value IN (3,4)) AND meta_key='vehicle_mileage' and meta_value=9)
    OR 
(meta_key='vehicle_mileage' and meta_value IN (10, 11))

Therefore the vehicle_make portion is impossible due to meta_value NEVER being both (3 OR 4) AND 9.

I think what you're going for is:

(meta_key='vehicle_make' and meta_value IN (3,4))
    OR 
(meta_key='vehicle_mileage' and meta_value IN (9, 10, 11))

All together now:

WHERE classifieds.id=classifieds_meta.classifieds_id AND category IN (2, 3, 4, 5)
AND (
    (meta_key='vehicle_make' and meta_value IN (3,4))
        OR 
    (meta_key='vehicle_mileage' and meta_value IN (9, 10, 11))
)

The takeaway from this is operator precedence. AND pairs get evaluated before OR.

Improve this answer

2 Comments

Oops left out some parens. :S Updated.
Now its working like charm, thanks alot bro... now if i want to add another condition called meta_key=vehicle_year and meta_value=2010, i just need to add OR (meta_key='vehicle_year' and meta_value IN (2010) after OR (meta_key='vehicle_mileage' and meta_value IN (9, 10, 11)) right?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.