• 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
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Joe Ess
  • salvin francis

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: 2865
370
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: 6546
174
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 "?".
 
Rancher
Posts: 4399
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: 10982
242
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer to use NOT EXISTS:
 
Dave Tolls
Rancher
Posts: 4399
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Heretic!
;)
 
rubbery bacon. crispy tiny ad:
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!