| Re: rdf help [message #17790 is a reply to message #17444] |
Mon, 19 April 2004 00:50   |
mocara
Messages: 157
Registered: January 2004
Karma:
|
Senior Member |
|
|
| Quote: |
SELECT
distinct m.thread_id, m.id, m.poster_id, m.reply_to, m.ip_addr, m.host_name, m.post_stamp, m.update_stamp, m.updated_by, m.icon, m.subject, m.attach_cnt, m.poll_id, m.foff, m.length,m.file_id, m.offset_preview, m.length_preview, m.file_id_preview, m.attach_cache, m.poll_cache, m.mlist_msg_id, m.msg_opt, m.apr,
u.alias,
t.forum_id,
p.name AS poll_name, p.total_votes,
m2.subject AS th_subject,
m3.subject AS reply_subject,
f.name AS frm_name,
c.name AS cat_name
FROM
fud26_msg m
INNER JOIN fud26_thread t ON m.thread_id=t.id
INNER JOIN fud26_forum f ON t.forum_id=f.id
INNER JOIN fud26_cat c ON c.id=f.cat_id
INNER JOIN fud26_msg m2 ON t.root_msg_id=m2.id
LEFT JOIN fud26_msg m3 ON m3.id=m.reply_to
LEFT JOIN fud26_users u ON m.poster_id=u.id
LEFT JOIN fud26_poll p ON m.poll_id=p.id
INNER JOIN fud26_group_cache g1 ON g1.user_id=0 AND g1.resource_id=f.id
WHERE
t.moved_to=0 AND m.apr=1 AND (g1.group_cache_opt & 2) > 0 GROUP BY m.thread_id ORDER BY m.post_stamp DESC LIMIT 20;
|
Just looking for ideas. I can see one flaw here. This will give me distinct thread but the first post in each thread and not the latest. distinct m.thread_id and group by m.thread_id being the basic changes.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]