[RESOLVED] mysql query syntax problem with join statement

ClarkF1

I'm writing a custom blog script and I want to get the latest blog entry and post it on the front page.

I have two tables comments and entries

The structure is as follows:

entries: pid (post no), user, time, entrytitle, entry
comments: cid (comment no), pid (no of related post), user, time, comment

I want to retrieve the user,time,entrytitle,entry and the number of associated comments. I've never used JOIN before.

"SELECT user,time,entrytitle,entry,COUNT(comment) AS 'comtotal' FROM entries JOIN ON entries.pid = comments.pid ORDER BY time DESC LIMIT 1";

When I try to query the db I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN ON entries.pid = comments.pid ORDER BY time DESC LIMIT 1'

I've tried comtotal with and without quotes without luck. I can't think what else it could be.

nnichols

Give this a try -

SELECT user,time,entrytitle,entry,COUNT(c.cid) AS 'comtotal'
FROM entries e
LEFT JOIN comments c USING(pid)
ORDER BY time DESC
LIMIT 1;

You were missing the second table name between JOIN and ON. I have used the USING syntax for the join instead of ON as it looks cleaner if the fields used have the same name in both tables. I have also changed the JOIN to be a LEFT JOIN so it will it will return the latest blog entry even if it has no comments yet. Your query would have returned the latest blog entry that had a comment, so it wouldn't have shown your latest entry if it had no comment.

Hope this helps 😉

mjax

nnichols wrote:
Give this a try -
SELECT user,time,entrytitle,entry,COUNT(c.cid) AS 'comtotal'
FROM entries e
LEFT JOIN comments c USING(pid)
ORDER BY time DESC
LIMIT 1;
You were missing the second table name between JOIN and ON. I have used the USING syntax for the join instead of ON as it looks cleaner if the fields used have the same name in both tables. I have also changed the JOIN to be a LEFT JOIN so it will it will return the latest blog entry even if it has no comments yet. Your query would have returned the latest blog entry that had a comment, so it wouldn't have shown your latest entry if it had no comment.

Hope this helps 😉

That SQL code will return each entry multiple times if there are multiple comments. You did bypass that by limiting the number of rows to one, but this is not a nice solution.

The code I'm proposing allows you to return all entries and their comment count or just one entry and it's count.

This should work if you are running MySQL 4.1 or higher (which supports subqueries):

SELECT
  entries.*, (SELECT COUNT(*) FROM comments WHERE comments.pid = entries.pid) AS comment_count
FROM
  entries
ORDER BY
  time DESC

Just extend it with a 'WHERE entries.pid = '$pid' if you are just after a specific entry.

nnichols

SELECT user,time,entrytitle,entry,COUNT(c.cid) AS 'comtotal'
FROM entries e
LEFT JOIN comments c USING(pid)
GROUP BY e.pid
ORDER BY time DESC
LIMIT 1;

Sorry, I forgot the GROUP BY. Don't use the sub query version above as the MySQL optimizer probably will not be able to optimize it as efficiently as the LEFT JOIN. Sub queries should only be used when you cannot figure out how to do it properly 😉

mjax

Never mind my comments... 🙂

nnichols

I'm sorry to pick an arguement. My intention was not to offend but to inform. The hint was passed to me from one of the mysql core developers on the MySQL forums. If you try both forms with large enough datasets you will see the difference (I have tried it). This query will not suffer dramatically from the sub query but with more complex examples the difference can be significant. It would seem to be good practice to understand and use appropriate JOINs when applicable even if the speed improvements are not measurable.

mjax

As I re-read the complete post, I have concluded that the JOIN/GROUP BY approach works best in this situation. That's why I removed my previous comments.

ClarkF1

Thanks folks.......nnichols's code worked.

But I'll use mjax's as it'll work with other features I'll need.