Need help with complicated SQL query

Anon

We have a comment board/forum. The database wasn't designed by me, but I have to work with them.

I'm trying to select the most recent comment threads within the past 7 days, henece the WHERE parameters.

The problem is, I get dupilicates. Ex. results below (html table). Table defs are at the end of this post.

It's seeing the comment_table.comment_id and story_table.id pairs and thinking it's unique since the pairs are unique. However, I need the comment_table.comment_id column to be unique.

Any help is appreciated?

<table border=1>
<tr class="header">
<th>story_id</th>
<th>comment_id</th>
<th>user_id</th>
<th>posting_time</th>
<th>headline</th>
<th>category</th>
<th>screen_name</th>
<th>full_name</th>
</tr>
<tr>
<td>2954 </td>
<td>3023 </td>
<td>1702619966 </td>
<td>2002-04-30 11:39:36 </td>
<td>Police Report: May 1, 2002 </td>
<td>news </td>
<td>Charlon </td>
<td>M.C. Gerick </td>
</tr>
<tr>
<td>2935 </td>
<td>3022 </td>
<td>705672667 </td>
<td>2002-04-30 11:37:56 </td>
<td>The Reader's Voice: 'Worst of the Campus Elections' </td>
<td>opinion </td>
<td> </td>
<td>Marc Valles </td>
</tr>
<tr>
<td>2935 </td>
<td>3021 </td>
<td>540500466 </td>
<td>2002-04-30 11:30:33 </td>
<td>The Reader's Voice: 'Worst of the Campus Elections' </td>
<td>opinion </td>
<td>JBSiverts </td>
<td>Jennifer Siverts </td>
</tr>
<tr>
<td>2936 </td>
<td>3020 </td>
<td>565226674 </td>
<td>2002-04-30 03:10:47 </td>
<td>The Reader's Voice: There's Nothing Funny About A L </td>
<td>opinion </td>
<td>hoebob </td>
<td>Robert Tinker </td>
</tr>
<tr>
<td>2936 </td>
<td>3019 </td>
<td>1841694118 </td>
<td>2002-04-30 02:29:40 </td>
<td>The Reader's Voice: There's Nothing Funny About A L </td>
<td>opinion </td>
<td>Jason Schock </td>
<td>Jason Schock </td>
</tr>
<tr>
<td>2936 </td>
<td>3017 </td>
<td>982991683 </td>
<td>2002-04-29 23:31:23 </td>
<td>The Reader's Voice: There's Nothing Funny About A L </td>
<td>opinion </td>
<td>Bob The Great </td>
<td>Bob The Great </td>
</tr>
<tr>
<td>648 </td>
<td>3016 </td>
<td>324943642 </td>
<td>2002-04-29 23:15:27 </td>
<td>David Attias' Lawyers Move to Dismiss Evidence, Cla </td>
<td>news </td>
<td>chali 2na </td>
<td>ber ber </td>
</tr>
<tr>
<td>2936 </td>
<td>3015 </td>
<td>1841694118 </td>
<td>2002-04-29 19:13:29 </td>
<td>The Reader's Voice: There's Nothing Funny About A L </td>
<td>opinion </td>
<td>Jason Schock </td>
<td>Jason Schock </td>
</tr>
<tr>
<td>2922 </td>
<td>3013 </td>
<td>1841694118 </td>
<td>2002-04-29 18:59:17 </td>
<td>Show Your True Colors: The Worst of the Campus Elec </td>
<td>opinion </td>
<td>Jason Schock </td>
<td>Jason Schock </td>
</tr>
<tr>
<td>2936 </td>
<td>3012 </td>
<td>676864739 </td>
<td>2002-04-29 18:50:46 </td>
<td>The Reader's Voice: There's Nothing Funny About A L </td>
<td>opinion </td>
<td> </td>
<td>Tony Biasotti </td>
</tr>
<tr>
<td>2924 </td>
<td>3011 </td>
<td>1841694118 </td>
<td>2002-04-29 18:49:56 </td>
<td>Author Puts History of I.V. on Internet </td>
<td>news </td>
<td>Jason Schock </td>
<td>Jason Schock </td>
</tr>
<tr>
<td>2902 </td>
<td>3009 </td>
<td>358281295 </td>
<td>2002-04-29 18:31:35 </td>
<td>The Reader's Voice: Sex Columnist Should Learn That </td>
<td>opinion </td>
<td>Keir DuBois </td>
<td>Keir DuBois </td>
</tr>
<tr>
<td>2921 </td>
<td>3008 </td>
<td>358281295 </td>
<td>2002-04-29 18:24:03 </td>
<td>The Reader's Voice: March for Economic Justice and  </td>
<td>opinion </td>
<td>Keir DuBois </td>
<td>Keir DuBois </td>
</tr>
<tr>
<td>2921 </td>
<td>3007 </td>
<td>1027662447 </td>
<td>2002-04-29 18:18:04 </td>
<td>The Reader's Voice: March for Economic Justice and  </td>
<td>opinion </td>
<td>elkiki </td>
<td>henry sarria </td>
</tr>
<tr>
<td>2873 </td>
<td>3006 </td>
<td>1514748453 </td>
<td>2002-04-29 18:14:51 </td>
<td>The Reader's Voice: Long-Winded Nexus Column Presen </td>
<td>opinion </td>
<td>El Mosca </td>
<td>The Fly </td>
</tr>
<tr>
<td>2922 </td>
<td>3005 </td>
<td>1027662447 </td>
<td>2002-04-29 17:57:23 </td>
<td>Show Your True Colors: The Worst of the Campus Elec </td>
<td>opinion </td>
<td>elkiki </td>
<td>henry sarria </td>
</tr>
</table>

SELECT DISTINCT comment_table.story_id, comment_table.comment_id, comment_table.user_id, comment_table.posting_time,
story_table.headline, story_table.category,
user_table.screen_name, CONCAT(user_table.first_names, ' ', user_table.last_name) AS full_name
FROM comment_table LEFT JOIN story_table ON comment_table.story_id=story_table.id
INNER JOIN user_table ON user_table.user_id=comment_table.user_id
WHERE comment_table.posting_time > DATE_ADD( NOW(), INTERVAL -7 DAY)
ORDER BY comment_table.posting_time DESC

Below is a simplified version of our tables that include only what's used in SELECT statement.

+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| comment_id | int(20) | | PRI | NULL | auto_increment |
| story_id | int(11) | | | 0 | |
| user_id | int(11) | | | 0 | |
+----------------+--------------+------+-----+---------+----------------+

+----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| user_id | int(11) | | PRI | 0 | |
| first_names | varchar(100) | | | | |
| last_name | varchar(100) | | | | |
+----------------------+--------------+------+-----+---------+-------+

+------------------+-----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------------+------+-----+---------------------+-------+
| headline | text | YES | | NULL | |
| subhead | text | YES | | NULL | |
| id | int(11) unsigned | | PRI | 0 | |
+------------------+-----------------------+------+-----+---------------------+-------+

Anon

story_idcomment_id user_id headline
2954 3023 1702619966 Police Report: May 1, 2002
2935 3022 705672667 The Reader's Voice: 'Worst of the Campus Elections'
2935 3021 540500466 The Reader's Voice: 'Worst of the Campus Elections'
2936 3020 565226674 The Reader's Voice: There's Nothing Funny About A L
2936 3019 1841694118 The Reader's Voice: There's Nothing Funny About A L
2936 3017 982991683 The Reader's Voice: There's Nothing Funny About A L
648 3016 324943642 David Attias' Lawyers Move to Dismiss Evidence, Cla
2936 3015 1841694118 The Reader's Voice: There's Nothing Funny About A L
2922 3013 1841694118 Show Your True Colors: The Worst of the Campus Elec
2936 3012 676864739 The Reader's Voice: There's Nothing Funny About A L
2924 3011 1841694118 Author Puts History of I.V. on Internet
2902 3009 358281295 The Reader's Voice: Sex Columnist Should Learn That
2921 3008 358281295 The Reader's Voice: March for Economic Justice and
2921 3007 1027662447 The Reader's Voice: March for Economic Justice and
2873 3006 1514748453 The Reader's Voice: Long-Winded Nexus Column Presen
2922 3005 1027662447 Show Your True Colors: The Worst of the Campus Elec

Anon

I said that I need the comment_id to be unique. I meant, I need the headline column to be unique.

I will be using this this link to the thread, so I only need one result per thread.

djresonance

I think what you are looking for is the GROUP BY clause. Take a look at the url below which goes into detail about using it.

http://web.archive.org/web/20011123040452/www.arsdigita.com/books/sql/complex-queries.html

Good Luck,
Brandon

Anon

Brandon,

AWESOME! Thanks so much for pointing this out and for pointing me to that website.

Anon

Ok, I've got it working now. The statement now reads:

SELECT comment_table.story_id, comment_table.user_id, comment_table.posting_time, max(comment_table.posting_time) as most_recent,
story_table.headline, story_table.category,
user_table.screen_name, CONCAT(user_table.first_names, ' ', user_table.last_name) AS full_name
FROM comment_table LEFT JOIN story_table ON comment_table.story_id=story_table.id
LEFT JOIN user_table ON user_table.user_id=comment_table.user_id
WHERE comment_table.posting_time > DATE_ADD( NOW(), INTERVAL -7 DAY)
GROUP BY comment_table.story_id
ORDER BY most_recent DESC

The problem is, I need to find out how many comments a thread has total.

The above statement only tells me how many comments a thread has for the past 7 days.

Any help?

djresonance

It looks like the

WHERE comment_table.posting_time > DATE_ADD( NOW(), INTERVAL -7 DAY)

is limiting your date range. Since it seems that all the WHERE clause is doing is limiting the date range, you should be able to get rid of it altogether to get the desired results:

SELECT comment_table.story_id, comment_table.user_id, comment_table.posting_time, max(comment_table.posting_time) as most_recent,
story_table.headline, story_table.category,
user_table.screen_name, CONCAT(user_table.first_names, ' ', user_table.last_name) AS full_name
FROM comment_table LEFT JOIN story_table ON comment_table.story_id=story_table.id
LEFT JOIN user_table ON user_table.user_id=comment_table.user_id
GROUP BY comment_table.story_id
ORDER BY most_recent DESC

Try it out 🙂
Brandon

Anon

Well, I only want threads that have been active within the last 7 days, hence the WHERE clause.

However, for each thread, I want the total number of comments, not the the number of comments in the last 7 days.