table `forums` where register threads

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » table `forums` where register threads

table `forums` where register threads

Rate Topic

Display Mode

Topic Options

Author

Message

mrivero1961

Posted Tuesday, February 19, 2013 10:00 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:24 PM
Points: 12, Visits: 20

table `forums` where register threads

Hello everyone.

Thanks all in advance for any help and suggestions.
I've the table `forums` where register threads, I need in output:

1) all threads without replies;
2) for threads with replies, the last reply.

I tried this query but is a problem: in output not are visible the new threads without replies.

This version where use LEFT OUTER JOIN return this new output, the row # 2 -b.id = 394- is too ...

Please check this:

 SELECT
        A.ID,
	A.connected,
	B.id,
	B.connected,
	A.datum,
	B.datum_update,
	A.title,
	A.author,
	B.message,
	B.last_replies
FROM
	forums a
LEFT OUTER JOIN forums b ON a.id = b.connected
LEFT OUTER JOIN (
	SELECT
		connected,
		max(datum_update) AS max_date
	FROM
		forums
	GROUP BY
		connected
) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)
WHERE
	A.connected = 0
ORDER BY
	dt.max_date DESC;
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
| ID  | connected | id   | connected | datum               | datum_update        | title           | author      | message  | last_replies |
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
| 392 |         0 |  395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me         | Sandra      | regards  | Sammy        |
| 392 |         0 |  394 |       392 | 2012-08-16 11:19:16 | 2012-08-24 00:08:00 | help me         | Sandra      | hi there | admin        |
| 396 |         0 | NULL | NULL      | 2013-02-16 21:28:42 | NULL                | new thread      | Rudy        | NULL     | NULL         |
| 397 |         0 | NULL | NULL      | 2013-02-18 21:35:59 | NULL                | post new thread | swampBoogie | NULL     | NULL         |
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+
4 rows in set


-- ----------------------------
-- Table structure for [dbo].[forums]
-- ----------------------------
DROP TABLE [dbo].[forums]
GO
CREATE TABLE [dbo].[forums] (
[id] int NULL ,
[connected] int NULL ,
[datum] datetime NULL ,
[datum_updated] datetime NULL ,
[title] nvarchar(255) NULL ,
[author] nvarchar(255) NULL ,
[message] nvarchar(255) NULL ,
[last_replies] nvarchar(255) NULL 
)


GO

-- ----------------------------
-- Records of forums
-- ----------------------------
INSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'392', N'0', N'2012-08-16 11:19:16.000', N'2012-08-16 11:08:00.000', N'help me', N'Sandra', N'hello my friend', N'Sandra');
GO
INSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'394', N'392', N'2012-08-24 12:15:27.000', N'2012-08-24 00:08:00.000', N'help me', N'admin', N'hi there', N'admin');
GO
INSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'395', N'392', N'2013-01-24 13:17:27.000', N'2013-01-24 01:17:00.000', N'help me', N'Sammy', N'regards', N'Sammy');
GO
INSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'396', N'0', N'2013-02-16 21:28:42.000', N'2013-02-16 21:28:49.000', N'new thread', N'Rudy', N'this is new thread', N'Rudy');
GO
INSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'397', N'0', N'2013-02-18 21:35:59.000', N'2013-02-18 21:36:04.000', N'post new thread', N'swampBoogie', N'this is my new thread', N'swampBoogie');
GO

Post #1421741

Kingston Dhasian

Posted Wednesday, February 20, 2013 5:11 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 2,217, Visits: 4,173

change your WHERE Clause like below

 WHERE A.connected = 0 AND dt.connected IS NOT NULL

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1422027

mrivero1961

Posted Wednesday, February 20, 2013 7:30 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:24 PM
Points: 12, Visits: 20

Kingston Dhasian (2/20/2013)

change your WHERE Clause like below

 WHERE A.connected = 0 AND dt.connected IS NOT NULL

thank you for help, but the output is incorrect:

SELECT
	A.ID,
	A.connected,
	B.id,
	B.connected,
	A.datum,
	B.datum_update,
	A.title,
	A.author,
	B.message,
	B.last_replies
FROM
	forums a
LEFT OUTER JOIN forums b ON a.id = b.connected
LEFT OUTER JOIN (
	SELECT
		connected,
		max(datum_update) AS max_date
	FROM
		forums
	GROUP BY
		connected
) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)
WHERE
	A.connected = 0
AND dt.connected IS NOT NULL
ORDER BY
	dt.max_date DESC;
+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
| ID  | connected | id  | connected | datum               | datum_update        | title   | author | message | last_replies |
+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
| 392 |         0 | 395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards | Sammy        |
+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+
1 row in set

I need this output:

+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+
| id  | datum               | datum_update        | title           | author      | message               | last_replies | connected |
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+
| 395 | 2013-01-24 13:17:27 | 2013-01-24 01:17:00 | help me         | Sammy       | regards               | Sammy        | NULL      |
| 396 | 2013-02-16 21:28:42 | 2013-02-16 21:28:49 | new thread      | Rudy        | this is new thread    | Rudy         | NULL      |
| 397 | 2013-02-18 21:35:59 | 2013-02-18 21:36:04 | post new thread | swampBoogie | this is my new thread | swampBoogie  | NULL      |
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+

Post #1422081

Kingston Dhasian

Posted Wednesday, February 20, 2013 7:45 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 2,217, Visits: 4,173

This should help

WHERE a.connected = 0 AND ( dt.connected IS NOT NULL OR b.id IS NULL )

Even the below code will solve your requirement if you are using SQL Server 2005+

SELECT	*
FROM	forums AS a
LEFT OUTER JOIN	(
			SELECT	ROW_NUMBER() OVER ( PARTITION BY connected ORDER BY datum_updated DESC ) AS RN, *
			FROM	forums
		) b ON a.id = b.connected AND b.RN = 1
WHERE	a.connected = 0

Edit: Added alternate code

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1422089

mrivero1961

Posted Wednesday, February 20, 2013 7:53 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:24 PM
Points: 12, Visits: 20

Kingston Dhasian (2/20/2013)

This should help

WHERE a.connected = 0 AND ( dt.connected IS NOT NULL OR b.id IS NULL )

Thank you very much, now working!!! BigGrin

SELECT
	A.ID,
	A.connected,
	B.id,
	B.connected,
	A.datum,
	B.datum_update,
	A.title,
	A.author,
	B.message,
	B.last_replies
FROM
	forums a
LEFT OUTER JOIN forums b ON a.id = b.connected
LEFT OUTER JOIN (
	SELECT
		connected,
		max(datum_update) AS max_date
	FROM
		forums
	GROUP BY
		connected
) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)
WHERE
	a.connected = 0
AND (
	dt.connected IS NOT NULL
	OR b.id IS NULL
)
ORDER BY
	dt.max_date DESC;
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+
| ID  | connected | id   | connected | datum               | datum_update        | title           | author      | message | last_replies |
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+
| 392 |         0 |  395 |       392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me         | Sandra      | regards | Sammy        |
| 396 |         0 | NULL | NULL      | 2013-02-16 21:28:42 | NULL                | new thread      | Rudy        | NULL    | NULL         |
| 397 |         0 | NULL | NULL      | 2013-02-18 21:35:59 | NULL                | post new thread | swampBoogie | NULL    | NULL         |
+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+
3 rows in set

Post #1422096

« Prev Topic | Next Topic »

Permissions