asked on

Why can't do this Subquery?

SELECT comments.titulo, comments.post_id, Count(comments.titulo) AS CuentaDetitulo
FROM comments
GROUP BY comments.titulo, comments.post_id
HAVING (((comments.post_id) IN (SELECT TOP 5 comments.post_id, Max(comments.timestamp) AS MáxDetimestamp
FROM comments
GROUP BY comments.post_id
ORDER BY Max(comments.timestamp) DESC)));
----------------------------
Access tells me that the reserved word EXISTS is missing in the FROM clause.

1William

Change to:

SELECT comments.titulo, comments.post_id, Count(comments.titulo) AS CuentaDetitulo
FROM comments
GROUP BY comments.titulo, comments.post_id
HAVING (((comments.post_id) Exsits (SELECT TOP 5 comments.post_id, Max(comments.timestamp) AS MáxDetimestamp
FROM comments
GROUP BY comments.post_id
ORDER BY Max(comments.timestamp) DESC)));

1William

When you have a subquery that can return more than one record, IN does not work, you need to use Exists. :)

stevbe

and as William pointed out you need it to say Exists and not Exsits :-)

SELECT comments.titulo, comments.post_id, Count(comments.titulo) AS CuentaDetitulo
FROM comments
GROUP BY comments.titulo, comments.post_id
HAVING (((comments.post_id) EXISTS (SELECT TOP 5 comments.post_id, Max(comments.timestamp) AS MáxDetimestamp
FROM comments
GROUP BY comments.post_id
ORDER BY Max(comments.timestamp) DESC)));

Steve

mltolun

ASKER

I get SYNTAX ERROR when I put this:

SELECT comments.titulo, comments.post_id, Count(comments.titulo) AS CuentaDetitulo
FROM comments
GROUP BY comments.titulo, comments.post_id
HAVING (((comments.post_id) EXISTS (SELECT TOP 5 comments.post_id, Max(comments.timestamp) AS MáxDetimestamp
FROM comments
GROUP BY comments.post_id
ORDER BY Max(comments.timestamp) DESC)));

MrRobin

I love this, if you click on the Help button you get this message...

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's
FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)

This is an unexpected error. Please contact Microsoft Product Support Services for more information.

...Unexpected?! So unexpected that they have an error code, description and help for it! All very odd, kinda sounds like you might win something for finding it, all you have to do is call this number now...

MrRobin

The problem here is that you can only return one field in a subquery (the one you want to check against)...

SELECT comments.titulo, comments.post_id, Count(comments.titulo) AS CuentaDetitulo
FROM comments
GROUP BY comments.titulo, comments.post_id
HAVING (((comments.post_id) IN (SELECT TOP 5 comments.post_id
FROM comments
GROUP BY comments.post_id
ORDER BY Max(comments.timestamp) DESC)));

...might do the job.

1William

I just did a test, this runs as expected (no guessing)
SELECT comments.titulo, comments.post_id, Count(comments.titulo) AS CuentaDetitulo
FROM comments
GROUP BY comments.titulo, comments.post_id
HAVING (((comments.post_id) = (SELECT TOP 5 comments.post_id, Max(comments.timestamp) AS MáxDetimestamp
FROM comments
GROUP BY comments.post_id
ORDER BY Max(comments.timestamp) DESC)));

mltolun

ASKER

1William....I get the EXISTS alert again...

ASKER CERTIFIED SOLUTION

1William

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

walterecook

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: 1William {http:#9295563}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer