Using a join with three tables when a field might be null
Posted
by John
on Stack Overflow
See other posts from Stack Overflow
or by John
Published on 2010-03-22T21:56:51Z
Indexed on
2010/03/22
22:51 UTC
Read the original article
Hit count: 241
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"].'">'.$row["title"].'</a></td>';
echo '</tr>';
echo '<tr>';
echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>';
echo '</tr>';
}
echo "</table>";
© Stack Overflow or respective owner