?
Solved

MySql statement returning no records

Posted on 2014-08-28
2
Medium Priority
?
189 Views
Last Modified: 2014-08-28
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
0
Comment
Question by:stargateatlantis
2 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 2000 total points
ID: 40291822
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40291832
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question