See http://www.mysql.com/doc/J/O/JOIN.html
What you basically want is a left join between TalkTopics and TalkReplies.
Then any rows in TalkReplies that don't have a row in TalkTopics will come
back with all nulls (but they'll still make it through the query), i.e:
SELECT *, COUNT(TalkReplies.TopicID) as Comments
FROM TalkTopics LEFT JOIN TalkReplies
ON TalkTopics.TopicID = TalkReplies.TopicID
GROUP BY TalkTopics.TopicID DESC
-Mark
- Original Message -
From: Stanley, Jason [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, July 18, 2002 1:54 PM
Subject: count query across two tables
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
-
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
