hello all,
i'm new to the list (and to mysql, for that matter) and am looking for some help with what should be a fairly simple query. what i'm doing is writing a very simple bulletin board with jsp using two tables in mysql. the first table contains the title and body of the topic and the second contains the replies, which are cross-indexed by the topic id. simple enough, but here they are... +---------+------------------------------------+----------+-----------+ | TopicID | title | username | body | +---------+------------------------------------+----------+-----------+ | 1 | new topic | kilgore | body text | | 2 | big topic at hand | kilgore | more | | 3 | you'll never guess who i saw today | kilgore | david | | 4 | silliest topic ever | bob | etc... | | 5 | this is another topic | kilgore | ... | | 6 | look! my first topic! | jeff | ... | | 7 | yet another test topic | kilgore | ... | | 8 | NO MORE | kilgore | ... | | 9 | oh hello | kilgore | ... | | 10 | never mind the testing | kilgore | ... | | 11 | eh? | kilgore | ... | | 12 | TITLE | kilgore | ... | +---------+------------------------------------+----------+-----------+ +---------+---------+-------------------------+---------+ | ReplyID | TopicID | body | user | +---------+---------+-------------------------+---------+ | 4 | 4 | anyone? | bob | | 11 | 5 | what???? | kilgore | | 10 | 2 | who? | kilgore | | 8 | 4 | another reply | kilgore | | 9 | 3 | oh my | kilgore | | 12 | 5 | what???? | kilgore | | 13 | 5 | again | bob | | 14 | 3 | yessir | bob | | 15 | 6 | oi | jeff | | 16 | 6 | oi | jeff | | 17 | 6 | oi | jeff | | 18 | 6 | oi | jeff | | 19 | 6 | thing | kilgore | | 20 | 6 | thing | kilgore | | 21 | 6 | thing | kilgore | | 22 | 4 | BLING | tommy | | 23 | 10 | hello | kilgore | | 24 | 12 | reply | kilgore | +---------+---------+-------------------------+---------+ so here's my problem: on the page which displays the topic titles, i wish to display all of the topics along with the number of replies specific to that topic. here is my query: SELECT *, COUNT(TalkReplies.TopicID) as Comments FROM TalkTopics, TalkReplies WHERE TalkTopics.TopicID = TalkReplies.TopicID GROUP BY TalkTopics.TopicID DESC this gets me almost there BUT it excludes any topics from the list which have no replies (so i only get a list of 7 topics here instead of 12). this has been plaguing me for days and i can't seem to get my head around how i should rephrase the query. any insight would be *greatly* appreciated. thanks, -j --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
