Need to COUNT in LEFT JOIN using MYSQL

Question

Hi all

SELECT * 
FROM `tbl_group_join` tgj 
LEFT JOIN tbl_groups tg ON tg.group_id = tgj.group_id
LEFT JOIN tbl_registeration tr ON tr.mem_id = tgj.mem1_id 
WHERE tgj.group_id =24 LIMIT 0 , 30

Above query is working right. And it produces two rows as O/P. Exactly this is what I want.

Now in addition I need the count of ROWS. I did it by

SELECT count(mem_id)
FROM tbl_group_join 
WHERE group_id = '24' GROUP BY group_id.

But it makes the O/P in to one row. That is, I Ihe get O/P in one field as count as 2.

Now I am in need to get these two results in one QUERY.

Any possibilities

Thanks in Advance

Fero

Answer 1

SELECT *, (SELECT count(mem_id) FROM tbl_group_join WHERE group_id = '24' GROUP BY group_id ) as memAmount FROM tbl_group_join tgj LEFT JOIN tbl_groups tg ON tg.group_id = tgj.group_id LEFT JOIN tbl_registeration tr ON tr.mem_id = tgj.mem1_id WHERE tgj.group_id =24 LIMIT 0 , 30