mysql - SQL Query involving aggregate functions -

-

i having issues writing query. dont know if should use count because returns actual count , want return people havent done review. anyway here query trying write.

find users haven’t reviewed businesses.

the tables using are

reviews; +-------------+---------+------+-----+---------+-------+ | field       | type    | null | key | default | | +-------------+---------+------+-----+---------+-------+ | business_id | int(11) | no   | pri | null    |       | | user_id     | int(11) | no   | pri | null    |       | | review_id   | int(11) | no   | pri | null    |       |  | review_date | date    | yes  |     | null    |       | | star_rating | int(1)  | yes  |     | 1       |        businesses +--------------+--------------+------+-----+---------+-------+ | field        | type         | null | key | default | | +--------------+--------------+------+-----+---------+-------+ | business_id  | int(11)      | no   | pri | null    |       | | name         | varchar(50)  | yes  |     | null    |       | | city         | varchar(40)  | yes  |     | null    |       | | state        | varchar(20)  | yes  |     | null    |       | | full_address | varchar(120) | yes  |     | null    |       |  users; +------------+-------------+------+-----+---------+-------+ | field      | type        | null | key | default | | +------------+-------------+------+-----+---------+-------+ | user_id    | int(11)     | no   | pri | null    |       | | name       | varchar(50) | yes  |     | null    |       | | user_since | date        | yes  |     | null

here have far

select reviews.user_id reviews join businesses on (reviews.business_id = businesses.business_id) group reviews.user_id asc having count(*) > 0;

this returns 0 results , dont think right because can user , not write review. dont know else could. thanks

edit: figured out last query trying complete one! 

find users have reviewed every business.

you want users table not business table.

by left joining reviews table users table saying - me users , if haven't left review leave columns nulls. in clause, selecting results review columns nulls, thereby selecting users haven't left review.

select u.user_id       users u     left join reviews r         on r.user_id = u.user_id r.review_id null

Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

gradle error "Cannot convert the provided notation to a File or URI" -

-
i have defined 2 system properties in gradle.properties: systemprop.builddir=build systemprop.cfgdir=build\\cfg and have following task defined in build.gradle: task clean(group:'clean',description:'clean project') << { ant.sequential { delete(dir: system.properties.builddir) mkdir(dir: system.properties.builddir) delete(dir: system.properties.cfgdir) mkdir(dir: system.properties.cfgdir) } } this generates following error: execution failed task ':clean'. > cannot convert provided notation file or uri: {dir=build}. following types/formats supported: - string or charsequence path, e.g 'src/main/java' or '/usr/include' - string or charsequence uri, e.g 'file:/usr/include' - file instance. - uri or url instance. but equivalent block of code not generate error , works expected: task clean(group:'clean',description:'clean project') << { ...
Read more

[C++][SFML 2.2] Strange Performance Issues - Moving Mouse Lowers CPU Usage -

-
i've been working on making 2d map editor past 2 weeks or so, , i've run strange problem. trying optimize code, example, making functions should've been functions begin with, did made cpu usage go through roof. i've tried commenting out different sections of code thought culprits (scrolling, rendering, other calculations) no avail. think issue might trying call things within different functions, sake of making modular possible, i'd functionality. the other major change i've made since file i'm comparing i've ported variables external cpp file, since having variables global makes lot easier use within different functions. simple issue, can't life of me understand why happens. with original code, around 2-3% cpu usage. new code, around 20-35%, but, if move mouse around, it'll drop around 6-8%. tl;dr: after trying optimize code using functions, i've somehow killed performance, , found strange quirk moving mouse reduces cpu usage.. /...
Read more