Page 1 of 1
Simple Join Question
Posted: Sun Feb 22, 2009 9:54 pm
by llabeoll
I seem to be having a lot of trouble with joining two tables. I know there has to be a way to do this in one query. This is for a very rudimentary blog that I am playing around with for some more practice.
Table1 - blog
- blogid
- blogtitle
- blogpost
- date
Table2 - blogcomments
- commentid
- blogid
- comment
- commentname
I would like to show the all of the content from table blog and within that While loop of pulling that information, display the number of Comments which have a blogid equal to that of the blogid from the first table.
A Read More link which is located within the main blog entry will take the user then to the full article where the comments will be displayed in full under the post as well.
Any help would be greatly appreciated. Thank you.
Re: Simple Join Question
Posted: Mon Feb 23, 2009 5:50 am
by mintedjo
I would like to show the all of the content from table blog and within that While loop of pulling that information, display the number of Comments which have a blogid equal to that of the blogid from the first table.
I
think this is what you mean.
Code: Select all
SELECT blog.blogid, blogtitle, blogpost, blogdate, num_comments
FROM blog JOIN (
SELECT blogid, COUNT(*) AS num_comments
FROM blogcomments
GROUP BY blogid
) AS tmp ON tmp.blogid = blog.blogid
Re: Simple Join Question
Posted: Mon Feb 23, 2009 11:15 am
by llabeoll
mintedjo wrote:I would like to show the all of the content from table blog and within that While loop of pulling that information, display the number of Comments which have a blogid equal to that of the blogid from the first table.
I
think this is what you mean.
Code: Select all
SELECT blog.blogid, blogtitle, blogpost, blogdate, num_comments
FROM blog JOIN (
SELECT blogid, COUNT(*) AS num_comments
FROM blogcomments
GROUP BY blogid
) AS tmp ON tmp.blogid = blog.blogid
Thank you. I used this query and it returns 0 results as I believe it will only return blog posts which have comments. Here is my code. Any advice?
Code: Select all
$result = mysql_query("SELECT blog.blogid, blogtitle, blogpost, date, num_comments FROM blog JOIN (SELECT blogid, COUNT(*) AS num_comments FROM blogcomments GROUP BY blogid) AS tmp ON tmp.blogid = blog.blogid");
echo "<table width = '99%' align = 'right' cellpadding = '4' cellspacing = '0' border = '0'>";
while($row = mysql_fetch_array($result))
{
$example = $row['blogpost'];
echo "<tr>";
echo "<td colspan = '2'><p class = 'heading'>" . $row['blogtitle'] . "</p></td></tr>";
echo "<tr height = '5'>";
echo "<td colspan = '2' background = 'images/underline.jpg'></td></tr>";
echo "<tr>";
echo "<td width = '70%'><p align = 'justify'>";
echo blogsum($example,75);
echo " [...] <a href = 'blogdetails.php?id=" . $row['blogid'] . "'> Read More > ></a></p></td>";
echo "<td></td></tr>";
echo "<tr>";
echo "<td align = 'right' background = 'images/datebg.jpg'><p class = 'small'>[" . $row['num_comments'] . "] Comments /// Posted On: " . $row['date'] . "</p></td>";
echo "<td> </td>";
echo "</tr>";
}
echo "</table>";
Re: Simple Join Question
Posted: Tue Feb 24, 2009 8:26 am
by llabeoll
Can I get any more input on this?
Re: Simple Join Question
Posted: Tue Feb 24, 2009 4:48 pm
by llabeoll
Anybody want to shed some light on this for me?
Re: Simple Join Question
Posted: Tue Feb 24, 2009 4:51 pm
by VladSun
Use COUNT(), LEFT JOIN and GROUP BY
Re: Simple Join Question
Posted: Tue Feb 24, 2009 5:20 pm
by llabeoll
Thank you, very much. I was about to get it to work using the left join.