[musiccreatures]

Ok, so I am having a hard time finding the right query to generate a count of records from a table that
do not have any associated data from another table. For example, lets say that we have a forum module
that has 2 tables: Thread and response.

So a member posts a "thread", and other members can post one or more "response" to that "thread".
But not every "thread" will have a member post a response.
I need to be able to generate the count of "threads" which have no "response"'s.

Here is an example of the table data:
Table1

Code

CREATE TABLE threads (   thread_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   thread_title VARCHAR(75) NOT NULL DEFAULT 'TITLE',   thread_content TEXT NOT NULL DEFAULT '',   thread_band_id INT(7) UNSIGNED NOT NULL DEFAULT '0')

Table2

Code

CREATE TABLE responses (   response_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   response_title VARCHAR(75) NOT NULL DEFAULT 'TITLE',   response_content TEXT NOT NULL DEFAULT '',   response_band_id INT(7) UNSIGNED NOT NULL DEFAULT '0')   response_thread_id INT(7) UNSIGNED NOT NULL DEFAULT '1')

response_thread_id is the foreign key that ties any "response" to a "thread".

Any ideas or help would be greatly appreciated?

Thanks,
Tim McCune

[musiccreatures]

The answer to my own question

Code

SELECT t.thread_band_id, COUNT(r.response_id) unanswered   FROM threads t   LEFT JOIN responses r ON r.response_thread_id = t.thread_id   WHERE t.thread_band_id={$_user['user_band_id']}   GROUP BY thread_band_id;

Just in case someone else is trying to perform a similar task.

-Tim