I have two tables: accounts
and users
. users
references accounts
through account_id
.
What I want to do is get following query to work:
`SELECT accounts.*, count(users.*), user.* FROM accounts INNER JOIN users on accounts.id = users.account_id INNER JOIN users user on accounts.id = user.account_id limit 1 GROUP BY accounts.id`
So, I’ll get following results
`| accounts.id | users_count | user.phone | =========================================== | 1 | 5 | xxxxxx |
Basically, I want to get quantity of all users inside account and specific data for a single user. Is it even possible to do in a single query?
Advertisement
Answer
Assuming you want a single row for each user and account, you can do the following:
SELECT accounts.id,usercount.usercount,users.phone FROM accounts INNER JOIN users ON accounts.id = users.account_id INNER JOIN( SELECT count(*) usercount,users.account_id FROM users GROUP BY account_id ) usercount on usercount.account_id = accounts.id
If you want data for any single user within the account, you can use get MAX(users.id)
to get any user for an account and then fetch data for that user in the result.
SELECT accounts.id,usercount.usercount,users.phone FROM accounts INNER JOIN( SELECT count(*) usercount,users.account_id,MAX(users.id) as userid FROM users GROUP BY account_id ) usercount on usercount.account_id = accounts.id INNER JOIN users ON accounts.id = users.account_id AND usercount.userid = users.id