Syntax Error?

[JoeHoldcroft]

Does anyone have any ideas what I'm doing wrong in this long, complex query? I'm querying it in PHP and basically I use it to display the latest news item, all the joins are so I can give the amount of comments and the category name that it's posted in.

This is the query:
SELECT news.*, news.id AS news_id, news.date AS news_date, news_categories.name AS cat_name, COUNT(news_comments.*) AS num_comments FROM news, news_categories, news_comments WHERE news.category_id = news_categories.id AND news_comments.news_id = news.id ORDER BY news.date DESC LIMIT 1

This is the error it returns when I view the page:
An error occured (1064): 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 '*) AS num_comments FROM news, news_categories, news_comments WHERE news.category' at line 1

[russell]

SELECT n.*, n.id AS news_id, n.date AS news_date,
c.name AS cat_name, COUNT(m.news_id) AS num_comments
FROM news as n
INNER JOIN
news_categories as c
On n.category_id = c.id
INNER JOIN
news_comments as m
On m.news_id = n.id
GROUP BY
n.*, n.id, n.date, c.name
ORDER BY
n.date DESC LIMIT 1


May need to list out the field names from the news table in both the select and group by clauses too...

what dbms? mysql?

[JoeHoldcroft]

Yeah, MySQL.

I managed to get the query working, but now it won't return what I want it to.
I'm using PHP and querying this with mysql_fetch_array to get the latest news post and all its information, but it is returning nothing...

[NightShift58]

This part can't be right:GROUP BY
n.*, n.id, n.date, c.nameIt doesn't mean anything and if it did, it would mean GROUP BY every single field in the row.

[JoeHoldcroft]

Yeah I fixed that, it's not returning any SQL errors now, it just won't fetch the row.

[russell]

This part can't be right

group by every field not in the aggregate function. it isn't every field. he is counting from a field not in that list.

[NightShift58]

I can't get GROUP BY * to work in MySQL...

[russell]

thats why i said "May need to list out the field names" since he didsnt tell us the field names

:)

[NightShift58]

Ok, I was going mad over here...

[JoeHoldcroft]

:). Any ideas why it is returning no results?
This is the query at the moment:

SELECT n.*, n.id AS news_id, n.date AS news_date,
c.name AS cat_name, COUNT(m.news_id) AS num_comments
FROM news as n
INNER JOIN
news_categories as c
On n.category_id = c.id
INNER JOIN
news_comments as m
On m.news_id = n.id
GROUP BY
n.id, n.date, c.name
ORDER BY
n.date
DESC LIMIT 1
excuse the formatting :P