MySQL Nested Select with Joined Table

Question

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.

juergen d · Accepted Answer · 2018-04-23 10:59:42Z

1

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

answered Apr 23, 2018 at 10:59

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

Collectives™ on Stack Overflow

MySQL Nested Select with Joined Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related