SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


table `forums` where register threads


table `forums` where register threads

Author
Message
mrivero1961
mrivero1961
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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


Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6158 Visits: 5288
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/
mrivero1961
mrivero1961
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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 |
+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+


Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6158 Visits: 5288
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/
mrivero1961
mrivero1961
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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!!! :-D

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search