SQL Query involving aggregate functions

Question

I am having issues writing this query. I dont know if I should actually use count because that returns the actual count and I want to return people that havent done a review. Anyway here is my query that I am trying to write.

Find those users that haven’t reviewed any businesses.

The tables that I am using are

reviews;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| 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 | Extra |
+--------------+--------------+------+-----+---------+-------+
| 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 | Extra |
+------------+-------------+------+-----+---------+-------+
| user_id    | int(11)     | NO   | PRI | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| user_since | date        | YES  |     | NULL

Here is what I have so far

SELECT reviews.user_id FROM reviews
JOIN businesses ON (reviews.business_id = businesses.business_id)
GROUP BY reviews.user_id ASC
HAVING COUNT(*) > 0;

This returns 0 results and I dont think this is right because someone can be a user and not write a review. But I dont know what else I could. Thanks

EDIT: I figured out that last query but now I am trying to complete this one!

Find the users that have reviewed every business.

after ON you have reviews.business_id twice. Try switching one to businesses.business_id — Dan Hogan
– Dan Hogan, Commented Mar 16, 2015 at 20:40
If your EDIT is asking a different question, make a new posting, don't add on to this one. Also, please use SHOW CREATE TABLE, it is more descriptive than DESCRIBE TABLE. — Rick James
– Rick James, Commented Mar 17, 2015 at 16:19

G B · Accepted Answer · 2015-03-16 20:50:58Z

1

You want the users table not the business table.

By left joining the reviews table to the users table we are saying - get me all users and if they haven't left a review just leave those columns as NULLs. then in the where clause, we are selecting only those results where the review columns are nulls, thereby selecting users who haven't left a review.

select u.User_ID 
from 
    users u
    left join reviews r
        on r.user_id = u.user_id
where r.review_id is null

edited Mar 16, 2015 at 20:50

answered Mar 16, 2015 at 20:43

G B

1,47210 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bobbly Fillay Over a year ago

This didnt work for me. Can you explain it any more? I will add the user table to OP

G B Over a year ago

When you say it didn't work - what happened? error? unexpected results?

Bobbly Fillay Over a year ago

ERROR 1052 (23000): Column 'user_id' in field list is ambiguous

G B Over a year ago

Does that make sense? I can clarify further if needed.

Bobbly Fillay Over a year ago

I understand it now thanks. How would I find users that have reviewed every business?

|

Javaluca · Accepted Answer · 2015-03-16 20:51:52Z

0

SELECT u.*
FROM users u
WHERE NOT EXISTS ( SELECT 'a'
                   FROM reviews r
                   WHERE r.user_id = u.user_id
                 )

answered Mar 16, 2015 at 20:51

Javaluca

8571 gold badge6 silver badges13 bronze badges

1 Comment

showdev Over a year ago

A little explanation would be helpful.

Collectives™ on Stack Overflow

SQL Query involving aggregate functions

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related