Need help with SQL query to return latest threads but not from ignored users....
 

Hey all,

I am attempting to modify the excellent "most recent threads" hack so that threads that were just responded to by someone on the ignore list are not shown. Before changing the code I am trying to figure out what SQL I need, so I have been testing with direct queries.

The closest I have gotten is this:

select post.*, thread.* from post,thread where post.userid not in ('275','135','1') AND post.threadid = thread.threadid order by post.dateline desc

In this case the "('275','135','1')" are the three sample user IDs I am "ignoring".

This works.. but the problem is that because it orders by posts, if there is more than one recent response to the same thread, the thread appears twice. I can't figure out how to get it to show only one thread no matter how many recent posts it has.

Can anyone help?

Thanks.

so you tring to get lastest threads but not from ignored users
am i right ?

and please use php/sql tags in codes postings

Rightio, I'm leaving for a couple of days now. I'll give you a hand when I get back providing no-one has helped you before then :)

Correct miz. Sorry about not using the right tags.

Thanks Dean, hoping I can find some help sooner but if not, that will be nice. :)

I realized I can "kludge" around this by keeping track of the threads already seen in the loop that processes the results, but that's ugly.)

can't you select distinct?

distinct doesnt work because the post information is different.

c

add this before the latest threads code:

and then in the latest threads code, add:
just before:

Thanks. I found a kludgy solution already but that looks much more elegant. I need to take a look and see if I can adapt it tomorrow.

Just checked this again, it doesn't do what I need because it ignores whole threads based on the thread starter. I need it to ignore on a post-by-post basis. Thanks anyway though.

What about this:

[sql]select post.*, thread.* from post,thread where post.userid not in ('275','135','1') AND post.threadid = thread.threadid group by thread.threadid order by post.dateline desc[/sql]