MySql statement returning no records

I have the following SQL statement here.  Problem is in the comment table there is no data in there.  There is a screenshot for the result.  If there is no comments it should just have zero in the column instead of showing no records.  Yes there is records in the database.

SELECT idea_users.first_name, 
	idea_users.last_name, 
	ideas.idea, 
	ideas.cost,
	IFNULL(COUNT(comments.fk_idea_id), 0) as cnt
FROM ideas INNER JOIN idea_users ON ideas.fk_user_id = idea_users.user_id
	 INNER JOIN comments ON ideas.idea_id = comments.fk_idea_id  group by ideas.idea_id

Open in new window

result.png
stargateatlantisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Try using a left join to the comments table:
SELECT idea_users.first_name, 
	idea_users.last_name, 
	ideas.idea, 
	ideas.cost,
	IFNULL(COUNT(comments.fk_idea_id), 0) as cnt
FROM ideas INNER JOIN idea_users ON ideas.fk_user_id = idea_users.user_id
	 LEFT JOIN comments ON ideas.idea_id = comments.fk_idea_id  group by ideas.idea_id

Open in new window

A left join means that records from the other tables are still returned when the join doesn't succeed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
You may want to get a chart depicting SQL joins, this one by C.L. Moffatt is very nice:
image:
http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

e.g. [tableA] in the depiction would equate to [ideas] in your question. When using a left join you will get 100% of TableA plus any information that is shared between them.
Venn diagram of left join
article:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.