Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Angry Unanswered: Display unique records on querying 3 tables

    I have 3 tables and i want to display
    the combination of some fields of the 3 tables into the result.

    p table fields (Posts table for a forum):
    author_id
    topic_id
    forum_id
    post_date

    t table fields (Topic Table for a forum):
    title
    views
    posts

    f table fields (main Forum table):
    id
    name

    My SQl statement:

    SELECT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
    FROM p, f, t
    WHERE f.id = t.forum_id AND p.topic_id = t.tid
    ORDER BY p.post_date DESC
    Limit 0, 30

    the results give the last 30 postings in descending order and displayed on the website.

    Problem:
    i only want unique topic_id to be displayed within the latest 30 posts, now if more than one person discuss the same topic within the 30 latest post, both posts will be listed.

    p.author_id p.topic_id p.forum_id p.post_date t.title t.views
    323 123 general 05/08/04 mysql 45
    345 123 general 04/08/04 mysql 45
    346 111 advanced 04/08/04 sybase 119

    In the above table, i want the result to display only first and 3rd line.
    How to resolve the above problem?

    ----

    On this problem i tried the sql as follows:

    SELECT p.author_id, p.topic_id, p.forum_id, p.post_date, p.icon_id, t.title, t.views, t.posts, f.name

    FROM p, f, t

    WHERE f.id = t.forum_id AND p.topic_id = t.tid

    GROUP BY p.topic_id

    ORDER BY p.post_date DESC

    The results is almost there but instead of showing the lates posts
    by members, it shows order posts.

    The Group By sucessfully filtered out the duplicate post title,
    the it seems to me the ORDER BY p.post_date DESC has not effect-
    can't grab the latest post made by members,
    the system does not return the latest entry, but rather seems randomly.

    Many Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hi,

    The following query should do what you want, but may be slow if you have many rows in your p table (p2 is another alias for p) :

    SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
    FROM p, f, t
    WHERE f.id = t.forum_id AND p.topic_id = t.tid
    AND p.post_date=(select max(p2.post_date) from p2 where p2.topic_id=p.topic_id)
    ORDER BY p.post_date DESC
    Limit 0, 30

    Tell me if it works as you want it to.

  3. #3
    Join Date
    Aug 2004
    Posts
    4
    appreciate your help so much, but I got an error when doing the query :

    SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
    FROM posts p, forums f, topics t
    WHERE f.id = t.forum_id AND p.topic_id = t.tid AND p.post_date = (
    SELECT max( p2.post_date )
    FROM posts p2
    WHERE p2.topic_id = p.topic_id )
    ORDER BY p.post_date DESC
    LIMIT 0 , 30

    MySQL said:
    #1064 - You have an error in your SQL syntax near 'SELECT max( p2.post_date )
    FROM posts p2
    WHERE p2.topic_id = p.topi' at line 4

    any idea what's wrong ?

  4. #4
    Join Date
    Aug 2004
    Posts
    4
    and here's another query that does not produce what i want

    SELECT
    p.author_id,
    p.topic_id,
    p.forum_id,
    p.post_date,
    p.icon_id,
    t.title,
    t.views,
    t.posts,
    f.name
    FROM
    posts p,
    forums f
    INNER JOIN topics t ON t.last_post = p.post_date
    WHERE f.id = t.forum_id
    GROUP BY p.topic_id
    ORDER BY p.post_date DESC";

    Because of the Group by topic, it show all distinct lines but
    not the latest being shown (seems like the order by post_date can't help here)

    Any help is appreciated !!

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Ok. Using MAX without a GROUP BY Clause works in Oracle, but seems not to work in MySQL (in Oracle there is nothing like 'LIMIT 0 , 30'). Who says "Normalized SQL ?"

    By the way, in your case, you should in my opinion avoid a group by. This should work fine, I think :

    SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
    FROM posts p, forums f, topics t
    WHERE f.id = t.forum_id AND p.topic_id = t.tid AND p.post_date >= ALL(
    SELECT p2.post_date
    FROM posts p2
    WHERE p2.topic_id = p.topic_id )
    ORDER BY p.post_date DESC
    LIMIT 0 , 30

  6. #6
    Join Date
    Aug 2004
    Posts
    4
    Thanks RBARAER! You certainly saved me!

    I just noticed that MySQL 4.0 Stable does not support sub-query!
    That's why i got the errors! and Only MySQL 4.1.3 beta support subquery!!
    I installed the beta version and tried your suggestions, both worked but the results are very different! your second suggestion executed 100 times faster.
    Guess the Max slow down the whole thing.

    SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
    FROM posts p, forums f, topics t
    WHERE f.id = t.forum_id AND p.topic_id = t.tid
    AND p.post_date=(select max(p2.post_date) from posts p2 where p2.topic_id=p.topic_id)
    ORDER BY p.post_date DESC
    Limit 0, 30

    took 152,281 msec

    SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
    FROM posts p, forums f, topics t
    WHERE f.id = t.forum_id AND p.topic_id = t.tid AND p.post_date >= ALL(SELECT p2.post_date
    FROM posts p2 WHERE p2.topic_id = p.topic_id ) ORDER BY p.post_date DESC LIMIT 0 , 30;

    Took 1,875 msec !!

    Thanks once again for the help! Really appreciate your advice!

    Dex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •