Win a copy of AWS Security this week in the Cloud/Virtualization forum!
  • 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
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

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.

 
Sheriff
Posts: 3123
459
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus 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: 7108
184
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: 4592
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: 12030
257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer to use NOT EXISTS:
 
Dave Tolls
Rancher
Posts: 4592
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Heretic!
;)
 
The human mind is a dangerous plaything. This tiny ad is pretty safe:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic