CODEWITHSUNDEEP
mysqljoincountgroup-by
lozadaOmr
lozadaOmr

Reputation: 2655

Implementing Count() and Group By() in MySQL using two Tables

I have two separate Tables tbl_name and tbl_parent.

This is what my database looks like:

tbl_name 

member_id   first_name   last_name
    0          John        Doe
    1          Jane        Doe
    2          Julie       Doe
    3          Billy       Joe

tbl_parent

 member_id   parent_id
    0          Null
    1           0
    2           0
    3          Null

As you can see tbl_name holds data about the members, while tbl_parent is used to define the relationship of each record in tbl_name.

The parent_id in tbl_parent is just referencing the member_id, so based on the given table, John Doe is the parent of both Jane Doe and Julie Doe.

Now I'm trying to count all those who have parent_id 0,

this was my query, it worked fine when they are not separated in a different table.

select *, count(parent_id) from tbl_parent group by parent_id

The difficulty I'm having is working with two tables I can't get it to display correctly

I would like the output to be:

member_id   first_name   last_name  child
    0          John         Doe       2
    1          Jane         Doe       0
    2          Julie        Doe       0
    3          Billy        Joe       0

Upvotes: 1

Views: 1394

Answers (3)

Himanshu Sharma
Himanshu Sharma

Reputation: 254

You can achieve this by simple use of Left join:

Try this:

SELECT c.*, COUNT(p.member_id) child FROM tab_name c LEFT JOIN tbl_parent p ON c.member_id = p.parent_id GROUP BY c.member_id

Please have a look following link for more knowledge about Joins in MySQL http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

Upvotes: 2

elixenide
elixenide

Reputation: 44831

Try this:

SELECT tbl_name.*, COUNT(tbl_parent.member_id) AS child
FROM tab_name
LEFT JOIN tbl_parent ON tbl_name.member_id = tbl_parent.parent_id
GROUP BY tbl_name.member_id

Upvotes: 2

rs.
rs.

Reputation: 27427

Try this

select n.*, count(parent_id) Child
from tbl_name n
left outer join tbl_parent p on n.member_id = p.parent_id
GROUP BY n.member_id, first_name, last_name

Upvotes: 1

Related Questions