0

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.

Ross
  • 112
  • 1
  • 17

1 Answers1

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
juergen d
  • 201,996
  • 37
  • 293
  • 362