1

This one has me pretty confused and after reading numerous forums and "how to" sites, I think I'm even more confused why this isn't working.

I have 2 tables that I am joining.

Table 1 (products) contains all my product details.

Table 2 (ratings) contains all the ratings for each product and each product has 3 ratings from 3 different rating entities.

I am working on a set of AJAX search filters and need to write the query in a manner that incorporates all of the filters.

The filters include price, number of reviews, average review stars, rating 1, rating 2, rating 3.

The price, num reviews, and avg stars comes from Table 1 (products).

Rating 1, 2, and 3 come from Table 2 (ratings).

I need something like this for my query, but this is returning zero results when I know (have verified in the database) that I have records that meet the criteria I'm querying for.

SELECT p.id, p.name 
FROM products p 
JOIN ratings r ON p.id = r.id 
WHERE p.num_reviews BETWEEN '0' AND '1000' 
AND p.price BETWEEN '0' AND '500' 
AND p.avg_rvw_stars BETWEEN '0' AND '5' 
AND (r.ratingSource='1' AND (r.rating BETWEEN '1' AND '4')) 
AND (r.ratingSource='2' AND (r.rating BETWEEN '0' AND '100')) 
AND (r.ratingSource='3' AND (r.rating BETWEEN '0' AND '100')) 
ORDER BY p.name ASC

The issue seems to reside in the last three WHERE statements. If I remove any 2 of the 3, I get results. But when I include all 3, I get nothing when there are records that qualify.

If I change the last two AND's to OR, I also get results, but it's not the result set that is expected or needed.

Am I not writing this correctly?

Any help/guidance is greatly appreciated!!

Improve this question
2
  • What happens if you switch the "AND" to an "OR" for teh ratingsource 1 2 and 3 Commented Aug 26, 2012 at 12:45
  • @Rmilligan: Do the records you have (with ratings 1, 2 and 3) are in 3 different rows of the table? Commented Aug 26, 2012 at 12:52

3 Answers 3

Reset to default
3

Your problem is that you confuse the meaning of AND and OR.

You are looking for things that have, for instance 

r.ratingSource='1'

and a bit later they ALSO should have

r.ratingSource='2'

There is NOTHING that will be valid for that, because it is a single thing that can NOT be both of them. So you need to think about what AND does.

If you have some stuff EACH result must have, you should use AND. If you have groups of stuff they might have, you can do (thing AND otherthing) OR (thing2 and otherthing2)

It's all basic logic really :)

I guess you probably mean this:

SELECT p.id, p.name 
FROM products p 
JOIN ratings r ON p.id = r.id 
WHERE p.num_reviews BETWEEN '0' AND '1000' 
AND p.price BETWEEN '0' AND '500' 
AND p.avg_rvw_stars BETWEEN '0' AND '5' 
AND (
        (r.ratingSource='1' AND (r.rating BETWEEN '1' AND '4')) 
     OR (r.ratingSource='2' AND (r.rating BETWEEN '0' AND '100')) 
     OR (r.ratingSource='3' AND (r.rating BETWEEN '0' AND '100'))
) 
ORDER BY p.name ASC
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I'm following you, but changing the last two AND's to OR's gives me a result set that is not accurate and way beyond what should be returned. What I'm looking for is ratingSource=1 and between 1-4 AND ratingSource=2 and between 0-100 AND ratingSource=3 and between 0-100. I have records that qualify for that. So, your answer is basic logic - yes - but it doesn't help me solve my problem.
The thing you should do is check what will be valid for your logic. You example you now give is that you did not add any brackets, so the "OR" is for everything. It should help you solve your problem if you don't just stop after fixing the first step, but also think about why the second step might be wrong. I'll add what I think you mean as a query, but it really is better if you understand what is going on. That will help you solve your problem..
How can a row have ratingScore=2 as well as ratingScore=3? That is not possible.
There are 3 rows for each product in the ratings table. Each row is for each separate rating entity.
@Rmilligan2372: The WHERE clause filters per row. So every row have to match all conditions defined in WHERE. I think you should change your database schema.
2

If you have 3 different rows with the ratings, you can't use that logic. Each row is checked for the WHERE conditions once, so there cannot be a row with a rating both 1 and 2.

The easiest way is to join thrice:

SELECT p.id, p.name 
FROM products p 
  JOIN ratings r1 ON  p.id = r1.id 
                  AND r1.ratingSource = 1
                  AND r1.rating BETWEEN 1 AND 4
  JOIN ratings r2 ON  p.id = r2.id 
                  AND r2.ratingSource = 2
                  AND r2.rating BETWEEN 0 AND 100
  JOIN ratings r3 ON  p.id = r3.id
                  AND r3.ratingSource = 3
                  AND r3.rating BETWEEN 0 AND 100
WHERE p.num_reviews BETWEEN 0 AND 1000 
  AND p.price BETWEEN 0 AND 500 
  AND p.avg_rvw_stars BETWEEN 0 AND 5 
ORDER BY p.name ASC ;
Improve this answer

1 Comment

Thanks ypercube - you completely understood what I needed and after implementing your recommendation, it works beautifully! Thank you very much!
1

I think you need

AND 
(
     (r.ratingSource='1' AND (r.rating BETWEEN '1' AND '4')) 
      OR (r.ratingSource='2' AND (r.rating BETWEEN '0' AND '100')) 
      OR (r.ratingSource='3' AND (r.rating BETWEEN '0' AND '100')) 
    )
Improve this answer

Comments

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.