Geek Answers Handbook

MySQL Query Design for the latest forum post

I have these 3 tables

forums_forum

+-----+--------+-------------+-------+-----+
| fid | name   | description | index | cid |
+-----+--------+-------------+-------+-----+
|  36 | gdghdf | hjghj       |    54 |   5 |
|  45 | yutuy  | iuyi        |    99 |   6 |
+-----+--------+-------------+-------+-----+

forums_threads

+----+-----+-----+-------+-------+------+-----------+------+
| id | tid | fid | moved | mfrom | view | important | lock |
+----+-----+-----+-------+-------+------+-----------+------+
|  1 |   4 |  36 |     0 | NULL  |    0 |         0 |    0 |
|  2 |  12 |  36 |     0 | NULL  |    7 |         0 |    0 |
|  3 |   9 |  15 |     0 | NULL  |    0 |         0 |    0 |
+----+-----+-----+-------+-------+------+-----------+------+

forums_posts

+----+-------+--------+--------+---------------------+--------+--------+-----+
| id | title | detail | author | date                | edited | editby | tid |
+----+-------+--------+--------+---------------------+--------+--------+-----+
|  1 | asfsd | sdfsd  |      1 | 2010-07-01 21:31:29 |      0 | NULL   |   4 |
+----+-------+--------+--------+---------------------+--------+--------+-----+

I am trying to create a query that returns a result -> for each unique 'fid', one line from 'forums_posts' (ORDER BY' date).

forums_forum. fid= forums_threads. fid forums_threads. tid= forums_posts.tid

thank

+5
source share
3 answers

This is the venerable largest-n-on-group problem that often occurs when stack overflows. Here's a solution based on your tables:

SELECT p.* FROM forums_posts p JOIN forums_threads t ON p.tid = t.tid
WHERE NOT EXISTS (
    SELECT * FROM forums_posts p2 JOIN forums_threads t2 ON p2.tid = t2.tid
    WHERE t.fid = t2.fid AND p.date < p2.date
);
+3
source

Well, I offer you some invitations.

SELECT C.date, C.title, A.name
FROM forums_forum A 
   JOIN forums_threads B ON A.fid=B.fid 
   JOIN forums_posts C ON B.tid=C.tid
ORDER BY C.date DESC LIMIT 1

or .. not verified:

SELECT MAX(c.date), C.date, C.title, A.name
FROM forums_forum A 
   JOIN forums_threads B ON A.fid=B.fid 
   JOIN forums_posts C ON B.tid=C.tid
LIMIT 1

; -)

p.s. , "date", "index" "view", .

p.p.s. ( , ), ?

+1

JOIN, forums_forums last_updated. UPDATE.

UPDATE SET last_updated = ()

, , SELECT .

SELECT * FROM forums_forum ORDER BY last_updated DESC

0

More articles:


All Articles