mySQL return no data [ query help ]

php1999

Hi All,

I am trying to build a query that will print the group name and total number of users in each group.

[code=php]Group | counter

Sport 14
Guest 7
Friends 0
employee 0
Family 3
[/code]

I used this query, but the mysql will not print the group name if there is no data on students table 🙁

SELECT groups.id, groups.title, count(students.id) as total
FROM groups, students
WHERE groups.id = students.gid
GROUP BY students.gid

and the result will be

Group            |              counter
----------------------------------------

Sport                                14
Guest                                7
Family                               3

Thanks in advance

Piranha

You should read up on joins. In this case a left join would be perfect:

SELECT groups.id, groups.title, count(students.id) as total
FROM groups
left join students on groups.id = students.gid
GROUP BY students.gid

sajan

Will your post your table structure . 🙂

php1999

Piranha;10884435 wrote:
You should read up on joins. In this case a left join would be perfect:
SELECT groups.id, groups.title, count(students.id) as total
FROM groups
left join students on groups.id = students.gid
GROUP BY students.gid

yes, it's work but the mysql will return one row only of missing groups !

How can I use IFNULL() in this case ?

Piranha

Then you need to group by something else. Try it out, you will solve it 🙂