StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMS Access sql query for forum tables
text
Body
copied!<p>If you will find this question to simple for the forum - blame it on me and I will apologize, because I am no more than just a novice to this issues.</p> <p>I have this two tables in MS Access: </p> <p>table Items and table Comments connected by Comments.parent ID = Items.ID.</p> <p>I need a query that will displayed last 10 records from Items table, each record containing</p> <pre>Item.id Item.title Item.text Item,date_modified count of Comments [if any] for the Item last Comments[if any] guestName last Comments[if any] date_modified </pre> <p>So far I have got something like this:</p> <pre> SELECT TOP 10 t4.id, t4.* FROM ( SELECT Items.id AS item_id , Items.*, t3.guestName , t3.modified AS comment_date ,(SELECT count(*) FROM Comments where parentid = Items.id) as comentscount FROM Items ,( SELECT t2.id as commentID, t2.guestName , t2.modified, t2.parentid FROM Comments as t2 ORDER BY t2.modified DESC ) as t3 WHERE (Items.id = t2.parentid AND t3.commentID = (SELECT max(id) FROM Comments where parentid = Items.id)) ORDER BY Items.modified DESC UNION SELECT Items.id AS item_id, Items.* , null AS guestName, null AS comment_date, (SELECT count(*) FROM Comments where parentid = Items.id) as comentscount FROM Items WHERE (SELECT count(*) FROM Comments where parentid = Items.id) = 0 ) AS t4 </pre> <p>Well. It is working but I keep asking myself if this could be done in a more simple way.</p> <p>Any suggestions will be more then welcome.</p> <p>Thank you in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload