Using a join with three tables when a field might be null

Question

Hello,

The code below works great. It combines data from two MySQL tables. I would like to modify it by pulling in some data from a third MySQL table called "comment."

In the HTML table below, "title" is a field in the MySQL table "submission." Every "title" has a corresponding "submissionid" field. The field "submissionid" is also found in the "comment" MySQL table.

In the HTML table below, I would like "countComments" to equal the number of times a field called "commentid" appears in the MySQL table "comment" for any given "submissionid," where the "submissionid" is the same in both the "submission" and "comment" tables, and where the "submissionid" corresponds to the "title" being used.

Here's the catch: if there is no row in the MySQL table "comment" that corresponds with the "submissionid" being used for "table", I would like "countComments" to equal to zero.

How can I do this?

Thanks in advance,

John

$sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, l.username
             FROM submission AS s,
                  login AS l
            WHERE s.loginid = l.loginid
         ORDER BY s.datesubmitted DESC
            LIMIT 10";


$result = mysql_query($sqlStr);

$arr = array(); 
echo "<table class=\"samplesrec\">";
while ($row = mysql_fetch_array($result)) { 
    echo '<tr>';
    echo '<td class="sitename1"><a href="http://www.'.$row["url"].'"&gt;'.$row["title"].'&lt;/a&gt;&lt;/td&gt;';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'"&gt;'.$row["username"].'&lt;/a&gt;&lt;a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'"&gt;'.$row["countComments"].'&lt;/a&gt;&lt;/td&gt;';
    echo '</tr>';
    }
echo "</table>";

Answer 1

If I understand your issue correctly, how about:

SELECT s.loginid, s.title, s.url, s.displayurl, l.username, COUNT(c.id) AS commentCount
FROM submission AS s
LEFT JOIN login AS l ON l.loginid = s.loginid
LEFT JOIN comments AS c ON c.submissionid = s.id
ORDER BY s.datesubmitted DESC
LIMIT 10

Answer 2

You are doing an INNER JOIN, but you need an OUTER JOIN, in particular, a LEFT JOIN. With a LEFT JOIN, the table "on the left" is always joined, and if there is no correspondence the fields of the table on the right are set to null. The difference is explained very well in this wikipedia page.

Then you have to group the rows with the same submission id and count how much rows have been grouped, taking care that if one submission has only one comment and another have none they both have 1 grouped row... If one submission has no comments, in the next query c.submissionid will be null, so

Your SQL could be

SELECT s.loginid, s.title, s.url, s.displayurl, l.username, c.submissionid, 
IF(c.submissionid IS NULL, 0, COUNT(*))  AS countComments
FROM submission AS s
INNER JOIN login AS l ON l.loginid = s.loginid
LEFT JOIN comments AS c ON c.submissionid = s.id
GROUP BY (s.id)
ORDER BY s.datesubmitted DESC
LIMIT 10

There can be errors, I have not tested the query... But I hope to have given you the right idea, that is the difference between outer joins and inner joins.

Answer 3

try

SELECT s.loginid, s.title, s.url, s.displayurl, l.username, c.submissionid, 
       COUNT(c.submissionid) AS countComments
FROM submission AS s
INNER JOIN login AS l ON l.loginid = s.loginid
LEFT JOIN comments AS c ON c.submissionid = s.id
GROUP BY s.loginid, s.title, s.url, s.displayurl, l.username, c.submissionid
ORDER BY s.datesubmitted DESC
LIMIT 10

or

SELECT s.loginid, s.title, s.url, s.displayurl, l.username, c.submissionid, 
       (SELECT count(*) FROM submission s WHERE c.submissionid =s.submissionid) AS countComments
FROM submission AS s
INNER JOIN login AS l ON l.loginid = s.loginid
ORDER BY s.datesubmitted DESC
LIMIT 10

providing I didn't make (m)any typos :)

If it works for you I can provide more explanations.