So I have three tables.
1) business:
-> id (varchar) id of business
-> name (varchar) name of business
2) review:
-> business_id (varchar) id of reviewed business
-> user_id (varchar) id of user who reviewed
3) user:
-> id (varchar) id of user
-> name (varchar) name of user
-> review_count (int) number of reviews written by the user
I want to write a query that extracts the business.ids and business.names of those businesses that have been reviewed by those users who have written more than 10 reviews.
I thought about doing an inner join on user and review on the condition user.id = review.user_id where user.id in (select id from user where review_count > 10). This would perform an inner join on the above tables after selecting only those user.id's that belong to users who have written more than 10 reviews. Next I wish to perform an inner join on this new table and the business table, on the condition that business.id = new_table.business_id, where new_table is the table obtained by performing the previous inner join on review and user.
I understand that there could be a problem with my renaming syntax.
I am getting a syntax error : Error: near "where": syntax error. I am using sqlite3.
And here is what I have so far.
select business.id, business.name
from business inner join
(
review inner join user
on review.user_id = user.id
where user.id in (
select id
from user
where review_count > 10)
) as R
on R.business_id = business.id;
The query below should be the same as what you are trying to do.
select business.id, business.name
from business
inner join review on review.business_id = business.id
inner join user on user.id = review.user_id
where user.review_count > 10