UGG! - Access SQL Problem

[xangelusx]

This is driving me nuts. Maybe it can't be done but I would be forever indebted to whomever could crack this problem.

OK, it's a doozy so try to stay with me.

I've got a message board, similar to this. It is made up of Top Level Thread and Responses. All of the threads are in one database table called bbx_threads. This may have been my downfall, but since both Top Level Threads and Response contain the same set of fields it seemed pointless to have two seperate tables.

On the main page, I list the messages along with author, reply count and post date. (see img)

The SQL statement that I use works great - except: If a Response is marked as disabled (boolean flag in table) it will count towards the Reply Count but will not be displayed in the thread details. For instance, in the image above the second post, "Testing Repost 2", it is shown to have 5 responses. However, 3 of those are disabled meaning that the details page only shows 2 replies.

Before I go any further, let me post the SQL. The actual statement is much more complex, but for our purposes it can be simplified as such:

SELECT t1.thread_id, t1.thread_subject, Count(t2.thread_id) AS thread_reply_count

FROM bbx_threads AS t1

LEFT JOIN  bbx_threads AS t2 ON t1.thread_id = t2.thread_parent_link

WHERE t1.thread_timestamp >= DateAdd("d",-31,Now())
  AND t1.thread_forum_link = 1
  AND t1.thread_parent_link = 0
  AND t1.thread_disabled = 0

GROUP BY t1.thread_id, t1.thread_subject

ORDER BY t1.thread_id DESC;

So, you can see that to get the Reply Count I just do a count on the ID field of the same table.

Now the big question: How can I write this so that I get the count from t2 only on records where the thread_disabled flag is set to false? If I just add the rule to the WHERE clause then the query returns the right numbers but Top Level Threads that have no replies don't show up.

If anyone can help out, I'd be really greatful. I've uploaded a sample database for you to download.

Thanks,
Chris Bloom

[This message has been edited by xangelusx (edited 03-13-2002).]

 

[simongallop]

In the design view of the query, change the field [thread_disabled] to ThrDis: nz([t2]![thread_disabled],0)

HTH

 

[xangelusx]

Harry,

Add that to my WHERE clause or to my SELECT list?

And I assume you mean on my updated query where I specifically add the t2.thread_disabled clause, right?

SELECT t1.thread_id, t1.thread_subject, Count(t2.thread_id) AS thread_reply_count
FROM bbx_threads AS t1 LEFT JOIN bbx_threads AS t2 ON t1.thread_id = t2.thread_parent_link
WHERE t1.thread_timestamp >= DateAdd("d",-31,Now())
  AND t1.thread_forum_link = 1
  AND t1.thread_parent_link = 0
  AND t1.thread_disabled = 0
  AND t2.thread_disabled = 0
GROUP BY t1.thread_id, t1.thread_subject
ORDER BY t1.thread_id DESC;

[This message has been edited by xangelusx (edited 03-13-2002).]

 

[simongallop]

In the WHERE clause, instead of:

AND t2.thread_disabled = 0

should be

AND nz(t2.thread_disabled,0) = 0

 

[xangelusx]

Thanks Harry, that's awesome.

Umm, except: Well everything shows up except for one message. Message #17 has a single response but the response is disabled. Now #17 won't show up in the results. The only thing that would set this record apart from the others is that it is the only one where ALL of the responses have been disabled. Any thoughs?

I've uploaded an updated database.

Also, here is an update screen shot. Post #17 is the "Testing" post by Avine. If you compare this screen shot to the results of the new query you will see that it is missing from the results.

Thanks, Chris

[This message has been edited by xangelusx (edited 03-13-2002).]

 

[Pat Hartman]

When you have selection criteria for both sides of a join sometimes it needs to be applied separately and this is one of those cases. Create a query that selects rows where thread_disabled = 0. Then use that query in place of the second table in the join. this will prevent any of the unwanted records from being included in the first place.

As the query stands now, the selection criteria is being applied AFTER the join and that is why the rows from the left side are also being excluded.

 

[xangelusx]

Pat,

I'm not sure I follow you. Do you mean break it up into two queries or nest a query inside of the join statement? Could you provide an example. I feel like I'm so close to getting this.

Thanks.

 

[xangelusx]

Still looking for help on this guys.

I've got it narrowed down to this:

Top Level Threads in which all of the replies are disabled are excluded from the results.