How to write a select statement using a nested join

Question

I have been learning how to use nested joins but I can't seem to get them working for my current database.

I have created these tables:

I want to first find out how many users have made at least one usage (they have some minutes in their account), created their account in 2016, and used an iPhone in 2013. I've tried which is producing an error:

SELECT COUNT(user_id) FROM USER
WHERE usage_count > 0
AND account_created <= ‘2016-12-31’
AND account_created >= ‘2016-01-01’
INNER JOIN PRODUCT
WHERE product = 'iPhone';

INNER JOIN PRODUCT ------ you're missing the columns to join ON — WillardSolutions
– WillardSolutions, Commented Dec 23, 2016 at 2:50
There's no usage_count column in any of your tables, so what is usage_count > 0 supposed to refer to? — Barmar
– Barmar, Commented Dec 23, 2016 at 2:53
Oops, missed that. It would be better if you posted text instead of images, so we can use the browser's search function. — Barmar
– Barmar, Commented Dec 23, 2016 at 2:57

Rahul · Accepted Answer · 2016-12-23 02:58:50Z

1

Your query error out which is expected since you have two WHERE clause and USER is key word and shouldn't be used as identifier. I have escaped them using backtique better use a non key/reserve word as your column name. Your query should look like

SELECT COUNT(u.user_id) FROM `USER` u
INNER JOIN USAGE us on us.user_id = u.user_id
JOIN PRODUCT p ON us.product_id = p.product_id
WHERE p.product = 'iPhone'
AND u.usage_count > 0
AND u.account_created <= '2016-12-31'
AND u.account_created >= '2016-01-01'

edited Dec 23, 2016 at 2:58

answered Dec 23, 2016 at 2:51

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

8 Comments

Barmar Over a year ago

USER is a keyword, but it's not reserved, so it doesn't need to be escaped.

Barmar Over a year ago

You need to join with the usage table, since that's what links user and product.

Barmar Over a year ago

You've got the wrong types of quotes around the dates (because you copied them from the question).

Rahul Over a year ago

@Barmar, yes right it's key word. Change the wording in answer. Again I have just suggested the flaws in his posted query and not the actual query that would work.

Barmar Over a year ago

MySQL has so many keywords that are common words, it's hard to avoid using them as table and column names. E.g. everyone has a column named password.

|

Collectives™ on Stack Overflow

How to write a select statement using a nested join

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related