MYSQL issue with join query

Question

tbl_group

(group_id, user_id, group_name, group_image, group_description, doi)

(9, 28, 'bbb', '1339660354imagesv.jpg', 'dfdsfsdf', '2012-06-14 13:22:34'),
(11, 1, 'cccc', '', '', '2012-06-14 14:49:56'),
(22, 1, '000', '', '', '2012-06-14 15:31:43');

tbl_groupnews

news_id, user_id, group_id, group_news, doi

(1, 1, 22, 'hi \n', '2012-06-14 16:20:36'),
(2, 1, 22, 'hello', '2012-06-14 16:21:59'),
(3, 1, 22, '1111', '2012-06-14 16:25:13'),
(4, 1, 22, 'jj', '2012-06-14 16:34:41'),
(5, 28, 9, 'hi', '2012-06-15 09:48:47');

`tbl_groupmembers`

groupmember_id, group_id, adder, member, doi

(1, 9, 28, 1, '2012-06-14 13:22:35'),
(2, 9, 28, 66, '2012-06-14 13:22:35'),
(4, 11, 1, 2, '2012-06-14 14:49:56'),
(5, 11, 1, 28, '2012-06-14 14:49:56'),
(6, 11, 1, 62, '2012-06-14 14:49:56'),
(36, 22, 1, 28, '2012-06-14 16:15:41'),
(37, 22, 1, 62, '2012-06-14 16:16:55'),
(38, 22, 1, 66, '2012-06-14 16:18:35'),
(39, 22, 1, 70, '2012-06-14 16:19:33');

I run the query

  SELECT tbl_groups.*,
         COUNT (tbl_groupnews.news_id) AS cn1,
         COUNT (tbl_groupmembers.group_id) AS cn2
    FROM tbl_groups
    LEFT JOIN tbl_groupnews ON tbl_groups.group_id = tbl_groupnews.group_id
    LEFT JOIN tbl_groupmembers ON tbl_groupmembers.group_id = tbl_groups.group_id
   WHERE (tbl_groups.user_id = 28 OR tbl_groupmembers.member = 28)
GROUP BY tbl_groups.group_id

My requirement is that I need get all the values from group, count of news and count of members WHERE user_id = 28 or member id = 28.

Can anyone help me to find a solution?

Where is tbl_groupnews ?

jornare
– jornare

2012-06-15 07:00:21 +00:00
Commented Jun 15, 2012 at 7:00 — jornare
– jornare, Commented Jun 15, 2012 at 7:00
changed the table name "tbl_groupnews"

Nisanth Kumar
– Nisanth Kumar

2012-06-15 07:03:03 +00:00
Commented Jun 15, 2012 at 7:03 — Nisanth Kumar
– Nisanth Kumar, Commented Jun 15, 2012 at 7:03

Rahul · Accepted Answer · 2012-06-15 06:59:59Z

1

Try like this:

SELECT tbl_groups.*,          
COUNT (tbl_groupnews.news_id) AS cn1,          
COUNT (tbl_groupmembers.group_id) AS cn2     
FROM tbl_groups     
LEFT JOIN tbl_groupnews ON tbl_groups.group_id = tbl_groupnews.group_id 
AND tbl_groups.user_id = 28    
LEFT JOIN tbl_groupmembers ON tbl_groupmembers.group_id = tbl_groups.group_id  
AND tbl_groupmembers.MEMBER = 28  
GROUP BY tbl_groups.group_id

answered Jun 15, 2012 at 6:59

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nisanth Kumar Over a year ago

Hi if we delete (36, 22, 1, 28, '2012-06-14 16:15:41') from tbl_groupmembers then the third group will again appear. If we delete above row the group id 22 will not appear in our result

jornare · Accepted Answer · 2012-06-15 07:24:05Z

0

If you select all columns from tbl_groups, they need to be in your group by statement

SELECT tbl_groups.*,COUNT(distinct tbl_groupnews.news_id) as cn1,
        COUNT(distinct tbl_groupmembers.group_id) as cn2
    FROM tbl_groups
    LEFT JOIN tbl_groupnews ON tbl_groups.group_id=tbl_groupnews.group_id
    LEFT JOIN tbl_groupmembers ON tbl_groupmembers.group_id=tbl_groups.group_id
    WHERE (tbl_groups.user_id  = 28 or tbl_groupmembers.member=28)
    GROUP BY tbl_groups.group_id,tbl_groups.user_id, tbl_groups.group_name, tbl_groups.group_image, tbl_groups.group_description, tbl_groups.doi

edited Jun 15, 2012 at 7:24

answered Jun 15, 2012 at 7:07

jornare

2,89321 silver badges28 bronze badges

1 Comment

jornare Over a year ago

I'm sorry I have no time to test it, but adding 'distinct' to the count should do some magic :)

Collectives™ on Stack Overflow

MYSQL issue with join query

tbl_group

tbl_groupnews

`tbl_groupmembers`

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

tbl_group

tbl_groupnews

tbl_groupmembers

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

`tbl_groupmembers`