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:
http://www.metallica2.com/stuff/test_dump.sql



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:
http://www.metallica2.com/stuff/query01.html

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:
http://www.metallica2.com/stuff/query02.html

....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.

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:
> http://www.metallica2.com/stuff/test_dump.sql
>
>
>
> 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:
> http://www.metallica2.com/stuff/query01.html
>
> 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:
> http://www.metallica2.com/stuff/query02.html
>
> ...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.
>

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:
http://www.metallica2.com/stuff/query03.html

Any ideas? Thank you.

--
Michal Stankoviansky

Michal Stankoviansky wrote:

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

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!

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:
> http://www.metallica2.com/stuff/query03.html
>
> Any ideas? Thank you.
>
> --
> Michal Stankoviansky
>

"Michal Stankoviansky" <michal.stankoviansky@gmail.com> wrote in message
news:1135962374.353170.198980@g14g2000cwa.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 (www.phpbb.com) which is an
open-source bulletin board written for PHP and MySQL. Many hosting service
providers support phpBB (e.g. www.101hosting.com). 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.

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