• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

Where statement returning values of ONLY

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to write a sql statement to return unique userid for reviews they posted where all of their reviews are '3 star' or greater. My current statement returns users that have made '3 star' reviews or greater, but also includes them if they've made reviews of less than. How should I fix it to exclude those who have made less than '3 star' reviews. A user can make multiple reviews which is why I thought DISTINCT userid would work.




I've tried this one below, but the syntax isn't right. I feel like I'm on the right track, but I'm not sure what to change.

 
Saloon Keeper
Posts: 2806
367
Android Eclipse IDE Angular Framework MySQL Database TypeScript Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like the star ratings are string values such as 1 star, 2 star, 3 star, etc., and then trying to compare to those strings.

Why not just use integer values such as 1, 2, 3, etc?  Then your where clause would just be: WHERE review_star >= 3
 
Sheriff
Posts: 6456
172
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's good advise, but the OP would still have the problem of when a user had a 3 star or greater review, but then had other reviews that were less than 3 stars.

I was thinking maybe use the ALL keyword, but in trying to construct the SQL, I ran into a problem I couldn't solve.

Maybe just use programming.  Use your first SQL statement to get all possible users, even if they have reviews less than 3 stars.  Then for each user in that list, create an SQL statement like
You would use a prepared statement to fill in the "?".
 
Master Rancher
Posts: 4371
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can do that as a single statement:



Something like that.
 
Saloon Keeper
Posts: 10875
235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer to use NOT EXISTS:
 
Dave Tolls
Master Rancher
Posts: 4371
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Heretic!
;)
 
Evacuate the building! Here, take this tiny ad with you:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!