CODEWITHSUNDEEP
mysqlselectnested-select
Ross
Ross

Reputation: 112

MySQL Nested Select with Joined Table

I'm assuming there's a way to do this with MySQL but my experience with relational databases is limited so I'm hoping to get some guidaince.

I have a users, registrations and user_registrations table. I'm want to create a SELECT query on my users table that does a nested select that counts the user_registrations for that user.

So for example, I would be looking to have something like this:

SELECT *, (SELECT COUNT() FROM user_registrations WHERE users.user_id = user_registrations.user_id) FROM users

I think my understanding of nested selects is off and I'm hoping someone could point me in the right direction here. Thanks.

Upvotes: 0

Views: 39

Answers (1)

juergen d
juergen d

Reputation: 204756

You need to group and include all columns you select from the users table into your group by clause also

SELECT u.id, u.name, COUNT(r.user_id) 
FROM users u
LEFT JOIN user_registrations r ON u.user_id = r.user_id 
GROUP BY u.id, u.name

Upvotes: 1

Related Questions