List of Titles and number of comments to be displayed

[buggiez]

Hi all,

i have 2 DBs: Energy, EnergyComments

Energy:
-id (pk)
-energytitle

EnergyComments:
-id (pk)
-energyid (fk)
-comment


Description of my program:
There is a list of energytitles in the DB. (for now lets say there are 10 records in it)

When a comment was given to the title, each comment will be saved in DB:EnergyComments.


Then, i would like to list the list of titles, plus the number of comments.
However, not all titles have comments, but still i want the titles to be displayed.

i had tried this sql statement:

Select e.title, count(c.id) as total
From energy e, energyComment c
Where e.id = c.energyid
group by e.title

and the results executed is a record of 2, which the titles consist of at least a comment. but what i want is that all the titles(10 of them) to be displayed regardless of the number of comments (whether its 0 or > 1).

any idea how should i alter the sql statement?

Thanks in advance!

 

[buggiez]

Hi i had finally got the solution myself.

 

[Paszt]

It's proper forum etiquette to post the solution when you find it yourself. This allows others to learn from your experience, the main reason for this forum (well that and to create advertising revenue for the site owner ).