Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

table `forums` where register threads Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 10:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:25 AM
Points: 14, Visits: 25
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
Posted Wednesday, February 20, 2013 5:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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
Posted Wednesday, February 20, 2013 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:25 AM
Points: 14, Visits: 25
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
Posted Wednesday, February 20, 2013 7:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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
Posted Wednesday, February 20, 2013 7:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:25 AM
Points: 14, Visits: 25
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

Post #1422096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse