board - (a better name would be board_posts) contains all the posts made by users in topics; first post in a topic has `answer_to` field set to zero; all replies in the topic have same value in `answer_to` field as the `id` of the first post in a topic --> board_topics - contains topics' subjects --> users - contains usernames Tables' structure: ================== --> table `board`: +-------------+----------------------+------+-----+ | Field | Type | Null | Key | +-------------+----------------------+------+-----+ | id | int(10) unsigned | NO | PRI | | user_id | smallint(5) unsigned | NO | | | time_posted | datetime | NO | MUL | | post_text | text | NO | | | answer_to | int(10) unsigned | NO | | +-------------+----------------------+------+-----+ Fields explanation: - user_id - user who posted the post - answer_to - contains id of the first post in the topic this post belongs to --> table `board_topics`: +---------------+----------------------+------+-----+ | Field | Type | Null | Key | +---------------+----------------------+------+-----+ | id | int(10) unsigned | NO | PRI | | topic_subject | varchar(200) | NO | | | categ_id | tinyint(1) unsigned | NO | | +---------------+----------------------+------+-----+ Fields explanation: - id - same value as the `id` of the first post in this topic in the `board` table - please ignore categ_id --> table `users`: +----------+----------------------+------+-----+ | Field | Type | Null | Key | +----------+----------------------+------+-----+ | id | smallint(5) unsigned | NO | PRI | | username | varchar(16) | NO | UNI | +----------+----------------------+------+-----+ What I want: ============ I want to get a resultset where each row would represent one discussion topic, with username of the user who created the topic (posted the first post of the topic and specified a topic subject), with text of this first post and in the same row username, text of the post and time of the post that is the newest reply in the topic. So I tried this self-join: SELECT b1.id, bt.topic_subject, b1.post_text, b1.time_posted, u1.username, b2.id, b2.post_text, u2.username, b2.time_posted, b2.answer_to FROM board b1 JOIN board_topics bt ON b1.id = bt.id JOIN users u1 ON b1.user_id = u1.id LEFT JOIN board b2 ON b2.answer_to = b1.id LEFT JOIN users u2 ON b2.user_id = u2.id ORDER BY b2.time_posted DESC, b2.id DESC ....which gives this: [url]http://www.metallica2.com/stuff/query01.html[/url] As you can see, I *almost* got what I wanted...but it contains ALL replies joined with the first post, and I only wanted to list the topics with the *most recent* reply LEFT JOIN-ed - each topic only once. So i tried to GROUP it BY the topic id [b1.id]: SELECT b1.id, bt.topic_subject, b1.post_text, b1.time_posted, u1.username, b2.id, b2.post_text, u2.username, b2.time_posted, b2.answer_to FROM board b1 JOIN board_topics bt ON b1.id = bt.id JOIN users u1 ON b1.user_id = u1.id LEFT JOIN board b2 ON b2.answer_to = b1.id LEFT JOIN users u2 ON b2.user_id = u2.id GROUP BY b1.id /* <--- added this line */ ORDER BY b2.time_posted DESC, b2.id DESC ....which gave me this: [url]http://www.metallica2.com/stuff/query02.html[/url] ....which looks a bit better but isn't correct, because first posts end up joined with the first reply (second post in the topic) insted of the most recent reply (the last post in the topic). I thought that some HAVING clause might help, but all I was getting were errors. Of course I could loop in the PHP code to get the most recent posts in the topics or maybe use a subquery, but I hoped there MUST be a better way to get these data. So...is this possible with a single statement? Thanks for reading through all this. Happy New Year to everyone. P.S.: If you know where I could read about some *real-world* examples of joins and grouping, I'd be grateful for a link. Thank you. [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <1135962374.353170.198980@g14g2000cwa.googlegroups.com> [ref] => [htmlstate] => on_nl2br [postusername] => Michal Stankoviansky [ip] => michal.stankovi [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> board - (a better name would be board_posts) contains all the posts > made by users in topics; first post in a topic has `answer_to` field > set to zero; all replies in the topic have same value in `answer_to` > field as the `id` of the first post in a topic > --> board_topics - contains topics' subjects > --> users - contains usernames > > > > Tables' structure: > ================== > > > --> table `board`: > > +-------------+----------------------+------+-----+ > | Field | Type | Null | Key | > +-------------+----------------------+------+-----+ > | id | int(10) unsigned | NO | PRI | > | user_id | smallint(5) unsigned | NO | | > | time_posted | datetime | NO | MUL | > | post_text | text | NO | | > | answer_to | int(10) unsigned | NO | | > +-------------+----------------------+------+-----+ > > Fields explanation: > - user_id - user who posted the post > - answer_to - contains id of the first post in the topic this post > belongs to > > > --> table `board_topics`: > > +---------------+----------------------+------+-----+ > | Field | Type | Null | Key | > +---------------+----------------------+------+-----+ > | id | int(10) unsigned | NO | PRI | > | topic_subject | varchar(200) | NO | | > | categ_id | tinyint(1) unsigned | NO | | > +---------------+----------------------+------+-----+ > > Fields explanation: > - id - same value as the `id` of the first post in this topic in the > `board` table > - please ignore categ_id > > > --> table `users`: > > +----------+----------------------+------+-----+ > | Field | Type | Null | Key | > +----------+----------------------+------+-----+ > | id | smallint(5) unsigned | NO | PRI | > | username | varchar(16) | NO | UNI | > +----------+----------------------+------+-----+ > > > > What I want: > ============ > > I want to get a resultset where each row would represent one discussion > topic, with username of the user who created the topic (posted the > first post of the topic and specified a topic subject), with text of > this first post and in the same row username, text of the post and time > of the post that is the newest reply in the topic. > > So I tried this self-join: > > SELECT > b1.id, > bt.topic_subject, > b1.post_text, > b1.time_posted, > u1.username, > b2.id, > b2.post_text, > u2.username, > b2.time_posted, > b2.answer_to > FROM board b1 > JOIN board_topics bt > ON b1.id = bt.id > JOIN users u1 > ON b1.user_id = u1.id > LEFT JOIN board b2 > ON b2.answer_to = b1.id > LEFT JOIN users u2 > ON b2.user_id = u2.id > ORDER BY b2.time_posted DESC, b2.id DESC > > ...which gives this: > [url]http://www.metallica2.com/stuff/query01.html[/url] > > As you can see, I *almost* got what I wanted...but it contains ALL > replies joined with the first post, and I only wanted to list the > topics with the *most recent* reply LEFT JOIN-ed - each topic only > once. > > So i tried to GROUP it BY the topic id [b1.id]: > > SELECT > b1.id, > bt.topic_subject, > b1.post_text, > b1.time_posted, > u1.username, > b2.id, > b2.post_text, > u2.username, > b2.time_posted, > b2.answer_to > FROM board b1 > JOIN board_topics bt > ON b1.id = bt.id > JOIN users u1 > ON b1.user_id = u1.id > LEFT JOIN board b2 > ON b2.answer_to = b1.id > LEFT JOIN users u2 > ON b2.user_id = u2.id > GROUP BY b1.id /* <--- added this line */ > ORDER BY b2.time_posted DESC, b2.id DESC > > ...which gave me this: > [url]http://www.metallica2.com/stuff/query02.html[/url] > > ...which looks a bit better but isn't correct, because first posts end > up joined with the first reply (second post in the topic) insted of the > most recent reply (the last post in the topic). > > I thought that some HAVING clause might help, but all I was getting > were errors. > > Of course I could loop in the PHP code to get the most recent posts in > the topics or maybe use a subquery, but I hoped there MUST be a better > way to get these data. So...is this possible with a single statement? > > Thanks for reading through all this. Happy New Year to everyone. > > P.S.: If you know where I could read about some *real-world* examples > of joins and grouping, I'd be grateful for a link. Thank you. >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <43B5720E.6070602@texeme.com> [ref] => <1135962374.353170.198980@g14g2000cwa.googlegroups.com> [htmlstate] => on_nl2br [postusername] => John A. Bailo [ip] => jabailo@texeme. [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Is self-join appropriate? - MySQL
Professional Web Applications Themes

Is self-join appropriate? - MySQL

Please forgive me if this post is too long. I was trying to solve this (probably simple) issue for all day but I'm not as good in SQL as I thought I was. I'm trying to learn JOINs in MySQL 5.0.16 on Windows XP. Let's say we have a simple web discussion board database. The 2 kB SQL dump for testing is available here: [url]http://www.metallica2.com/stuff/test_dump.sql[/url] Tables description: =================== --> board - (a better name would be board_posts) contains all the posts made by users in topics; first post in a topic has `answer_to` field set to zero; all replies in ...

  1. #1

    Default Is self-join appropriate?

    Please forgive me if this post is too long. I was trying to solve this
    (probably simple) issue for all day but I'm not as good in SQL as I
    thought I was.

    I'm trying to learn JOINs in MySQL 5.0.16 on Windows XP.

    Let's say we have a simple web discussion board database.

    The 2 kB SQL dump for testing is available here:
    [url]http://www.metallica2.com/stuff/test_dump.sql[/url]



    Tables description:
    ===================

    --> board - (a better name would be board_posts) contains all the posts
    made by users in topics; first post in a topic has `answer_to` field
    set to zero; all replies in the topic have same value in `answer_to`
    field as the `id` of the first post in a topic
    --> board_topics - contains topics' subjects
    --> users - contains usernames



    Tables' structure:
    ==================


    --> table `board`:

    +-------------+----------------------+------+-----+
    | Field | Type | Null | Key |
    +-------------+----------------------+------+-----+
    | id | int(10) unsigned | NO | PRI |
    | user_id | smallint(5) unsigned | NO | |
    | time_posted | datetime | NO | MUL |
    | post_text | text | NO | |
    | answer_to | int(10) unsigned | NO | |
    +-------------+----------------------+------+-----+

    Fields explanation:
    - user_id - user who posted the post
    - answer_to - contains id of the first post in the topic this post
    belongs to


    --> table `board_topics`:

    +---------------+----------------------+------+-----+
    | Field | Type | Null | Key |
    +---------------+----------------------+------+-----+
    | id | int(10) unsigned | NO | PRI |
    | topic_subject | varchar(200) | NO | |
    | categ_id | tinyint(1) unsigned | NO | |
    +---------------+----------------------+------+-----+

    Fields explanation:
    - id - same value as the `id` of the first post in this topic in the
    `board` table
    - please ignore categ_id


    --> table `users`:

    +----------+----------------------+------+-----+
    | Field | Type | Null | Key |
    +----------+----------------------+------+-----+
    | id | smallint(5) unsigned | NO | PRI |
    | username | varchar(16) | NO | UNI |
    +----------+----------------------+------+-----+



    What I want:
    ============

    I want to get a resultset where each row would represent one discussion
    topic, with username of the user who created the topic (posted the
    first post of the topic and specified a topic subject), with text of
    this first post and in the same row username, text of the post and time
    of the post that is the newest reply in the topic.

    So I tried this self-join:

    SELECT
    b1.id,
    bt.topic_subject,
    b1.post_text,
    b1.time_posted,
    u1.username,
    b2.id,
    b2.post_text,
    u2.username,
    b2.time_posted,
    b2.answer_to
    FROM board b1
    JOIN board_topics bt
    ON b1.id = bt.id
    JOIN users u1
    ON b1.user_id = u1.id
    LEFT JOIN board b2
    ON b2.answer_to = b1.id
    LEFT JOIN users u2
    ON b2.user_id = u2.id
    ORDER BY b2.time_posted DESC, b2.id DESC

    ....which gives this:
    [url]http://www.metallica2.com/stuff/query01.html[/url]

    As you can see, I *almost* got what I wanted...but it contains ALL
    replies joined with the first post, and I only wanted to list the
    topics with the *most recent* reply LEFT JOIN-ed - each topic only
    once.

    So i tried to GROUP it BY the topic id [b1.id]:

    SELECT
    b1.id,
    bt.topic_subject,
    b1.post_text,
    b1.time_posted,
    u1.username,
    b2.id,
    b2.post_text,
    u2.username,
    b2.time_posted,
    b2.answer_to
    FROM board b1
    JOIN board_topics bt
    ON b1.id = bt.id
    JOIN users u1
    ON b1.user_id = u1.id
    LEFT JOIN board b2
    ON b2.answer_to = b1.id
    LEFT JOIN users u2
    ON b2.user_id = u2.id
    GROUP BY b1.id /* <--- added this line */
    ORDER BY b2.time_posted DESC, b2.id DESC

    ....which gave me this:
    [url]http://www.metallica2.com/stuff/query02.html[/url]

    ....which looks a bit better but isn't correct, because first posts end
    up joined with the first reply (second post in the topic) insted of the
    most recent reply (the last post in the topic).

    I thought that some HAVING clause might help, but all I was getting
    were errors.

    Of course I could loop in the PHP code to get the most recent posts in
    the topics or maybe use a subquery, but I hoped there MUST be a better
    way to get these data. So...is this possible with a single statement?

    Thanks for reading through all this. Happy New Year to everyone.

    P.S.: If you know where I could read about some *real-world* examples
    of joins and grouping, I'd be grateful for a link. Thank you.

    Michal Stankoviansky Guest

  2. #2

    Default Re: Is self-join appropriate?


    Why not add a where clause with MAX or MIN time_posted?

    Michal Stankoviansky wrote:
    > Please forgive me if this post is too long. I was trying to solve this
    > (probably simple) issue for all day but I'm not as good in SQL as I
    > thought I was.
    >
    > I'm trying to learn JOINs in MySQL 5.0.16 on Windows XP.
    >
    > Let's say we have a simple web discussion board database.
    >
    > The 2 kB SQL dump for testing is available here:
    > [url]http://www.metallica2.com/stuff/test_dump.sql[/url]
    >
    >
    >
    > Tables description:
    > ===================
    >
    > --> board - (a better name would be board_posts) contains all the posts
    > made by users in topics; first post in a topic has `answer_to` field
    > set to zero; all replies in the topic have same value in `answer_to`
    > field as the `id` of the first post in a topic
    > --> board_topics - contains topics' subjects
    > --> users - contains usernames
    >
    >
    >
    > Tables' structure:
    > ==================
    >
    >
    > --> table `board`:
    >
    > +-------------+----------------------+------+-----+
    > | Field | Type | Null | Key |
    > +-------------+----------------------+------+-----+
    > | id | int(10) unsigned | NO | PRI |
    > | user_id | smallint(5) unsigned | NO | |
    > | time_posted | datetime | NO | MUL |
    > | post_text | text | NO | |
    > | answer_to | int(10) unsigned | NO | |
    > +-------------+----------------------+------+-----+
    >
    > Fields explanation:
    > - user_id - user who posted the post
    > - answer_to - contains id of the first post in the topic this post
    > belongs to
    >
    >
    > --> table `board_topics`:
    >
    > +---------------+----------------------+------+-----+
    > | Field | Type | Null | Key |
    > +---------------+----------------------+------+-----+
    > | id | int(10) unsigned | NO | PRI |
    > | topic_subject | varchar(200) | NO | |
    > | categ_id | tinyint(1) unsigned | NO | |
    > +---------------+----------------------+------+-----+
    >
    > Fields explanation:
    > - id - same value as the `id` of the first post in this topic in the
    > `board` table
    > - please ignore categ_id
    >
    >
    > --> table `users`:
    >
    > +----------+----------------------+------+-----+
    > | Field | Type | Null | Key |
    > +----------+----------------------+------+-----+
    > | id | smallint(5) unsigned | NO | PRI |
    > | username | varchar(16) | NO | UNI |
    > +----------+----------------------+------+-----+
    >
    >
    >
    > What I want:
    > ============
    >
    > I want to get a resultset where each row would represent one discussion
    > topic, with username of the user who created the topic (posted the
    > first post of the topic and specified a topic subject), with text of
    > this first post and in the same row username, text of the post and time
    > of the post that is the newest reply in the topic.
    >
    > So I tried this self-join:
    >
    > SELECT
    > b1.id,
    > bt.topic_subject,
    > b1.post_text,
    > b1.time_posted,
    > u1.username,
    > b2.id,
    > b2.post_text,
    > u2.username,
    > b2.time_posted,
    > b2.answer_to
    > FROM board b1
    > JOIN board_topics bt
    > ON b1.id = bt.id
    > JOIN users u1
    > ON b1.user_id = u1.id
    > LEFT JOIN board b2
    > ON b2.answer_to = b1.id
    > LEFT JOIN users u2
    > ON b2.user_id = u2.id
    > ORDER BY b2.time_posted DESC, b2.id DESC
    >
    > ...which gives this:
    > [url]http://www.metallica2.com/stuff/query01.html[/url]
    >
    > As you can see, I *almost* got what I wanted...but it contains ALL
    > replies joined with the first post, and I only wanted to list the
    > topics with the *most recent* reply LEFT JOIN-ed - each topic only
    > once.
    >
    > So i tried to GROUP it BY the topic id [b1.id]:
    >
    > SELECT
    > b1.id,
    > bt.topic_subject,
    > b1.post_text,
    > b1.time_posted,
    > u1.username,
    > b2.id,
    > b2.post_text,
    > u2.username,
    > b2.time_posted,
    > b2.answer_to
    > FROM board b1
    > JOIN board_topics bt
    > ON b1.id = bt.id
    > JOIN users u1
    > ON b1.user_id = u1.id
    > LEFT JOIN board b2
    > ON b2.answer_to = b1.id
    > LEFT JOIN users u2
    > ON b2.user_id = u2.id
    > GROUP BY b1.id /* <--- added this line */
    > ORDER BY b2.time_posted DESC, b2.id DESC
    >
    > ...which gave me this:
    > [url]http://www.metallica2.com/stuff/query02.html[/url]
    >
    > ...which looks a bit better but isn't correct, because first posts end
    > up joined with the first reply (second post in the topic) insted of the
    > most recent reply (the last post in the topic).
    >
    > I thought that some HAVING clause might help, but all I was getting
    > were errors.
    >
    > Of course I could loop in the PHP code to get the most recent posts in
    > the topics or maybe use a subquery, but I hoped there MUST be a better
    > way to get these data. So...is this possible with a single statement?
    >
    > Thanks for reading through all this. Happy New Year to everyone.
    >
    > P.S.: If you know where I could read about some *real-world* examples
    > of joins and grouping, I'd be grateful for a link. Thank you.
    >
    John A. Bailo Guest

  3. #3

    Default Re: Is self-join appropriate?

    Could you be more specific please? Nothing I tried worked.

    I tried this:

    SELECT
    b1.id,
    bt.topic_subject,
    b1.post_text,
    b1.time_posted,
    u1.username,
    b2.id,
    b2.post_text,
    u2.username,
    b2.time_posted,
    b2.answer_to
    FROM board b1
    JOIN board_topics bt
    ON b1.id = bt.id
    JOIN users u1
    ON b1.user_id = u1.id
    LEFT JOIN board b2
    ON b2.answer_to = b1.id
    LEFT JOIN users u2
    ON b2.user_id = u2.id
    WHERE b2.time_posted = MAX(b2.time_posted) /* <--- added this line */
    GROUP BY b1.id
    ORDER BY b2.time_posted DESC, b2.id DESC

    ....but that gives an error #1111: Invalid use of group function, I
    think because one can't use grouping functions in the WHERE clause.


    I also tried adding HAVING clause:

    SELECT
    b1.id,
    bt.topic_subject,
    b1.post_text,
    b1.time_posted,
    u1.username,
    b2.id,
    b2.post_text,
    u2.username,
    b2.time_posted,
    b2.answer_to
    FROM board b1
    JOIN board_topics bt
    ON b1.id = bt.id
    JOIN users u1
    ON b1.user_id = u1.id
    LEFT JOIN board b2
    ON b2.answer_to = b1.id
    LEFT JOIN users u2
    ON b2.user_id = u2.id
    GROUP BY b1.id
    HAVING b2.time_posted = MAX(b2.time_posted) /* <--- added this line */
    ORDER BY b2.time_posted DESC, b2.id DESC

    Which only returned one row...which, again, is not correct:
    [url]http://www.metallica2.com/stuff/query03.html[/url]

    Any ideas? Thank you.

    --
    Michal Stankoviansky

    Michal Stankoviansky Guest

  4. #4

    Default Re: Is self-join appropriate?

    Michal Stankoviansky wrote:
    > Which only returned one row...which, again, is not correct:
    > [url]http://www.metallica2.com/stuff/query03.html[/url]
    Ok, I think I see the flaw here.

    You want to return the row having the max/min date within the group, but
    your where/having clause is doing a global max/min on all articles for
    all dates!

    John A. Bailo Guest

  5. #5

    Default Re: Is self-join appropriate?

    Michal Stankoviansky wrote:
    > Could you be more specific please? Nothing I tried worked.
    You want to put the WHERE clauses in the joins -- that is where you are
    selecting the max, within each group, not at the very end (which makes
    it global).


    >
    > I tried this:
    >
    > SELECT
    > b1.id,
    > bt.topic_subject,
    > b1.post_text,
    > b1.time_posted,
    > u1.username,
    > b2.id,
    > b2.post_text,
    > u2.username,
    > b2.time_posted,
    > b2.answer_to
    > FROM board b1
    > JOIN board_topics bt
    > ON b1.id = bt.id
    > JOIN users u1
    > ON b1.user_id = u1.id
    > LEFT JOIN board b2
    > ON b2.answer_to = b1.id
    > LEFT JOIN users u2
    > ON b2.user_id = u2.id
    > WHERE b2.time_posted = MAX(b2.time_posted) /* <--- added this line */
    > GROUP BY b1.id
    > ORDER BY b2.time_posted DESC, b2.id DESC
    >
    > ...but that gives an error #1111: Invalid use of group function, I
    > think because one can't use grouping functions in the WHERE clause.
    >
    >
    > I also tried adding HAVING clause:
    >
    > SELECT
    > b1.id,
    > bt.topic_subject,
    > b1.post_text,
    > b1.time_posted,
    > u1.username,
    > b2.id,
    > b2.post_text,
    > u2.username,
    > b2.time_posted,
    > b2.answer_to
    > FROM board b1
    > JOIN board_topics bt
    > ON b1.id = bt.id
    > JOIN users u1
    > ON b1.user_id = u1.id
    > LEFT JOIN board b2
    > ON b2.answer_to = b1.id
    > LEFT JOIN users u2
    > ON b2.user_id = u2.id
    > GROUP BY b1.id
    > HAVING b2.time_posted = MAX(b2.time_posted) /* <--- added this line */
    > ORDER BY b2.time_posted DESC, b2.id DESC
    >
    > Which only returned one row...which, again, is not correct:
    > [url]http://www.metallica2.com/stuff/query03.html[/url]
    >
    > Any ideas? Thank you.
    >
    > --
    > Michal Stankoviansky
    >
    John A. Bailo Guest

  6. #6

    Default Re: Is self-join appropriate?

    "Michal Stankoviansky" <michal.stankoviansky> wrote in message
    news:1135962374.353170.198980g14g2000cwa.googlegr oups.com...
    > I want to get a resultset where each row would represent one discussion
    > topic, with username of the user who created the topic (posted the
    > first post of the topic and specified a topic subject), with text of
    > this first post and in the same row username, text of the post and time
    > of the post that is the newest reply in the topic.
    Hmm. Maybe something like this (I tested this with your sample data):

    SELECT bt.topic_subject,
    b1.id, b1.post_text, b1.time_posted, u1.username,
    COALESCE(b2.id, b1.id) AS last_posting_id,
    COALESCE(b2.post_text, b1.post_text) AS last_post_text,
    COALESCE(b2.time_posted, b1.time_posted) AS last_time_posted,
    COALESCE(u2.username, u1.username) AS last_username
    FROM board AS b1
    INNER JOIN users AS u1 ON b1.user_id = u1.id AND b1.answer_to = 0
    INNER JOIN board_topics AS bt ON b1.id = bt.id
    LEFT OUTER JOIN board AS b2 ON b1.id = b2.answer_to
    AND b2.time_posted >= ALL(
    SELECT bsub.time_posted
    FROM board AS bsub
    WHERE bsub.answer_to = b1.id)
    LEFT OUTER JOIN users AS u2 ON b2.user_id = u2.id
    ORDER BY b2.time_posted DESC, b2.id DESC

    The COALESCE() function calls handle the case when the thread consists of
    only one posting. The results of b2 will be NULL if no postings exist with
    answer_to equal to the id of the first posting in the article.

    BTW, I would have designed the schema a bit differently:
    - Don't use "id" as a field name. Joins are more clear if you're comparing
    user_id to user_id, post_id to post_id, and topic_id to topic_id.
    - The answer_to should reference the posting to which the current posting is
    in reply to, not the first posting in the thread. In your current design,
    you lose the ability to trace branches of the discussion.
    - Create an additional field in board to reference board_topics.topic_id.
    That's a better way to track the topic.
    - Use NULL as the answer_to signifier for a topic's first posting, instead
    of the value 0. Zero is a valid integer value, so using it as a signifier
    means you have to prime the table carefully.

    Another suggestion is to evaluate phpBB ([url]www.phpbb.com[/url]) which is an
    open-source bulletin board written for PHP and MySQL. Many hosting service
    providers support phpBB (e.g. [url]www.101hosting.com[/url]). If it does what you
    need, it'd be easier to use finished software than to spend many days
    implementing your own. I understand that such software often lacks a
    particular feature that you need, but it still may be easier to enhance
    working BB software than to implement your own.

    Regards,
    Bill K.


    Bill Karwin Guest

  7. #7

    Default Re: Is self-join appropriate?

    I finally got it:

    SELECT
    b1.id,
    bt.topic_subject,
    b1.post_text,
    b1.time_posted,
    u1.username,
    b2.id,
    b2.post_text,
    u2.username,
    b2.time_posted,
    b2.answer_to
    FROM board b1
    JOIN board_topics bt
    ON b1.id = bt.id
    JOIN users u1
    ON b1.user_id = u1.id
    LEFT JOIN board b2
    ON b2.answer_to = b1.id AND b2.time_posted = (SELECT
    MAX(b.time_posted) FROM board b WHERE b.answer_to = b1.id) /* <---
    added this subquery */
    LEFT JOIN users u2
    ON b2.user_id = u2.id
    GROUP BY b1.id
    ORDER BY b2.time_posted DESC, b2.id DESC


    Bill, just before posting this I found out you have posted your
    solution. I am going to read it now and will reply ASAP.

    Thank you all.

    -
    Michal

    Michal Stankoviansky Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. FMS Join two flv...
    By Luiz Alberto in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 23rd, 02:51 PM
  3. SQL join
    By sikhar in forum Coldfusion Database Access
    Replies: 15
    Last Post: April 20th, 04:21 PM
  4. DB2 join
    By Prakash in forum IBM DB2
    Replies: 1
    Last Post: September 10th, 05:24 PM
  5. How to Join...
    By CD in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 06:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139