February 19, 2013 at 10:00 am
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
February 20, 2013 at 5:11 am
change your WHERE Clause like below
WHERE A.connected = 0 AND dt.connected IS NOT NULL
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 7:30 am
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 |
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+
February 20, 2013 at 7:45 am
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*
FROMforums AS a
LEFT OUTER JOIN(
SELECTROW_NUMBER() OVER ( PARTITION BY connected ORDER BY datum_updated DESC ) AS RN, *
FROMforums
) b ON a.id = b.connected AND b.RN = 1
WHEREa.connected = 0
Edit: Added alternate code
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 7:53 am
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!!! 😀
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply