I'm getting error #1248 - Every derived table must have its own alias ,
below is the full msql statement please Help Im newbie many thanks. - SELECT `Posts`.`Post_id` AS `posts_id`, `Posts`.`title` AS `title`, LEFT(`Posts`.`body`, 512) AS `preview`, `comments`.`total_comments` AS `total_comments`, `category`.`Cat_id` AS `category_id`, `body`,`category`.`Cat_name`, DATE_FORMAT(`comments`.`last_comments`, `%d/%m/%y %H:%i:%s`) AS `last_comments`
-
FROM `Posts`
-
INNER JOIN `category` ON `category`.`Cat_id` = `Posts`.`Cat_id`
-
LEFT JOIN (SELECT `Post_id`, COUNT(`Comm_id`) AS `total_comments`, MAX(`comm_date`) AS `last_comment` FROM `comments`) GROUP BY `comments`.`Post_id` AS `comments`"
3  3723 
Hi Kelvinejimogu,
This part in your query should have alias -
SELECT `Post_id`, COUNT(`Comm_id`) AS `total_comments`, MAX(`comm_date`) AS `last_comment` FROM `comments
-
Like -
(SELECT `Post_id`, COUNT(`Comm_id`) AS `total_comments`, MAX(`comm_date`) AS `last_comment` FROM `comments) tab1
-
BTW I don't see condition for your LEFT join either on which you are joining with this query
Thanks guys it now working as it should be here is the full SQL - SELECT `Posts`.`Post_id` AS `posts_id`, `Posts`.`title` AS `title`, LEFT(`Posts`.`body`, 512) AS `preview`, `comments`.`total_comments` AS `total_comments`, `category`.`Cat_id` AS `category_id`, `body`,`category`.`Cat_name`, DATE_FORMAT(`comments`.`last_comments`, '%d/%m/%Y %H:%i:%s') AS `last_comments`
-
FROM `Posts`
-
INNER JOIN `category` ON `category`.`Cat_id` = `Posts`.`Cat_id`
-
LEFT JOIN (SELECT `Post_id`, COUNT(`Comm_id`) AS `total_comments`, MAX(`comm_date`) AS `last_comments` FROM `comments` GROUP BY `comments`.`Post_id`) AS `comments` ON `Posts`.`Post_id` = `comments`.`Post_id` ORDER BY `Post_date`
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Gary Hampson |
last post by:
I am trying to get the following:
"pull the last, oh, 1000 topics, and rank that population by board posted-to
and comments"
Here is the query as written for MSSQL:
SELECT dt.forum_id,...
|
by: Bill |
last post by:
I've created this:
SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
|
by: mirth |
last post by:
Hi all,
I have a table in this format
colname1 colname2 colname3
col1data1 col2data1 col3data1
col1data2 col2data2 col3data2
col1data3 col2data3 col3data3
col1data4 col2data4 col3data4
|
by: _link98 |
last post by:
Environment UDB 8.1 Fixpak 9 on Solaris (64bit instance).
After connecting to the database as "my_user".
There is an alias from my_user.mytable to XXX.mytable.
There is no real table...
|
by: blue875 |
last post by:
A tale of two queries:
One query looks like this, and runs fine. We'll call this the
"Customer1 query":
SELECT Customer1 overall.*,
IIf(IsNull(.),0,1) AS IsField,
IIf(IsNull(.),0,1) AS...
| | |
by: Baihao Yuan |
last post by:
Hi,
I got another question here:
I want to use query like this:
SELECT * FROM (sp_lock AS T) WHERE objectID = ...
The purpose is that I want to query the result set returned by sp_lock...
|
by: Andrew |
last post by:
Hi All
I wonder if anyone can help me with this.
The scenario is that I have a pair of related tables. One contains
record labels, the other contains contact names at those labels. In
the...
|
by: Stefano Sabatini |
last post by:
Hi all,
I'm facing this design problem.
I have a table which defines the behaviour of an Object in a given
state according to the type of event it is receiving.
So for each couple...
|
by: kkshansid |
last post by:
query 1=SELECT institute.state, result. *
FROM institute
INNER JOIN result ON centrecode = original_c
then i want to execute query on table 1
SELECT state, sum( IF( PASS_FAIL = 'P', 1, 0 ) ) AS...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| | |
