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.
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