I’ve queried user_invitations.inviter_id with filter I want which is:
select
inviter_id,
count(inviter_id) as invited_cnt
from user_invitations
group by
inviter_id
having
invited_cnt >= 52
order by
invited_cnt
From here I am trying to use only inviter_id to get id in another table called user (i.e. want to get user_id that match inviter_id I’ve just got above)
I’ve tried doing inner joing
from users
inner join (
select inviter_id, count(inviter_id) as invited_cnt
from user_invitations
group by inviter_id
having invited_cnt >= 52
order by invited_cnt
)user_inv
on users.id = user_inv.id;
no luck, I thought about using subquery
from users
where id in (
select inviter_id
from user_invitations
where inviter_id in (
select count(inviter_id) as invited_cnt
from user_invitations
group by inviter_id
having invited_cnt >= 52
)
group by inviter_id
);
but in most inner query it I cannot find out a way to return inviter_id after filtering by invited_cnt >= 52. How can I achieve this, is it not possible since to use having invited_cnt >= 52 we need aggreate count?
I’ve looked at some resources: Select id from one query and use it in another
but none of them seem to deal with aggregate.
Advertisement
Answer
If I understood your question, this query should give you the user_id
from user
that matched the inviter_id
SELECT
us.inviter_id,
count(us.inviter_id) AS invited_cnt,
u.user_id
FROM user_invitations AS us
JOIN user AS u ON u.user_id = us.inviter_id //just change the column name if needed
GROUP BY
us.inviter_id
HAVING
invited_cnt >= 52
ORDER BY
invited_cnt
If you are expecting only one user_id
, then I think this should work. Although it would be nice if you included your table structure plus some sample data and expected output