How to remove partially duplicate rows from select query's result set (DB Schema provided and query provided).

Hi,

 

Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.

Please provide separate solutions for SqlServer2000/2005. 

I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-

 

SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads

FROM

Forums f FULL JOIN Topics t ON f.forumid=t.forumid

FULL JOIN Threads th ON t.topicid=th.topicid

GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate

ORDER BY t.topicid ASC,th.lastpostdate DESC

 

Whose result set is as below:-

 

forumid

topicid name author lastpostdate NoOfThreads
1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2
1 1 Java Overall a@b.com 2008-01-27 14:44:29.000 2
1 2 JSP NULL NULL 0
1 3 EJB NULL NULL 0
1 4 Swings p@q.com 2008-01-27 15:12:51.000 1
1 5 AWT NULL NULL 0
1 6 Web Services NULL NULL 0
1 7 JMS NULL NULL 0
1 8 XML,HTML NULL NULL 0
1 9 Javascript NULL NULL 0
2 10 Oracle NULL NULL 0
2 11 Sql Server NULL NULL 0
2 12 MySQL NULL NULL 0
3 13 CSS NULL NULL 0
3 14 FLASH/DHTLML NULL NULL 0
4 15 Best Practices NULL NULL 0
4 16 Longue NULL NULL 0
5 17 General NULL NULL 0
  

On modifying the query to:-

 

SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads

FROM

Forums f FULL JOIN Topics t ON f.forumid=t.forumid

FULL JOIN Threads th ON t.topicid=th.topicid

GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate

HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)

ORDER BY t.topicid ASC,th.lastpostdate DESC

 

I get the result set as below:-

 

forumid

topicid name author lastpostdate NoOfThreads
1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2
1 4 Swings p@q.com 2008-01-27 15:12:51.000 1
 

I want the result set as follows:-

 

forumid

topicid name author lastpostdate NoOfThreads
1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2
1 2 JSP NULL NULL 0
1 3 EJB NULL NULL 0
1 4 Swings p@q.com 2008-01-27 15:12:51.000 1
1 5 AWT NULL NULL 0
1 6 Web Services NULL NULL 0
1 7 JMS NULL NULL 0
1 8 XML,HTML NULL NULL 0
1 9 Javascript NULL NULL 0
2 10 Oracle NULL NULL 0
2 11 Sql Server NULL NULL 0
2 12 MySQL NULL NULL 0
3 13 CSS NULL NULL 0
3 14 FLASH/DHTLML NULL NULL 0
4 15 Best Practices NULL NULL 0
4 16 Longue NULL NULL 0
5 17 General NULL NULL 0
  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 

The scripts for creating the tables and inserting test data is as follows in an already created database:-

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Forums]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Threads]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Topics]

GO

 

CREATE TABLE [dbo].[Forums] (

            [forumid] [int] IDENTITY (1, 1) NOT NULL ,

            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Threads] (

            [threadid] [int] IDENTITY (1, 1) NOT NULL ,

            [topicid] [int] NOT NULL ,

            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [replies] [int] NOT NULL ,

            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [lastpostdate] [datetime] NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Topics] (

            [topicid] [int] IDENTITY (1, 1) NOT NULL ,

            [forumid] [int] NULL ,

            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Forums] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [forumid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Threads] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [threadid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Topics] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [topicid]

            )  ON [PRIMARY]

GO

  

ALTER TABLE [dbo].[Threads] ADD

             FOREIGN KEY

            (

                        [topicid]

            ) REFERENCES [dbo].[Topics] (

                        [topicid]

            )

GO

 

ALTER TABLE [dbo].[Topics] ADD

             FOREIGN KEY

            (

                        [forumid]

            ) REFERENCES [dbo].[Forums] (

                        [forumid]

            )

GO

  

------------------------------------------------------

 

insert into forums(name,description) values('Developers','Developers Forum');

insert into forums(name,description) values('Database','Database Forum');

insert into forums(name,description) values('Desginers','Designers Forum');

insert into forums(name,description) values('Architects','Architects Forum');

insert into forums(name,description) values('General','General Forum');

 

insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');

insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');

insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');

insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');

insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');

insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');

insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');

insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');

insert into topics(forumid,name,description) values(1,'Javascript','Javascript');

insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');

insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');

insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');

insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');

insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');

insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');

insert into topics(forumid,name,description) values(4,'Longue','Longue');

insert into topics(forumid,name,description) values(5,'General','General Discussion');

 

insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');

insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');

insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');

 


-----------------------------------------------
Please click Mark as Answer if this helped.
0
aparnagarg
1/28/2008 1:01:51 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

7 Replies
590 Views

Similar Articles

[PageSpeed] 5

Hi,

Nice way of posting the issue (generate script and examples). Here is the query that does the job:

 

SELECT Topics.forumid, Topics.topicid, Topics.name, LastPosts.author, LastPosts.lastpostdate,
		(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Topics
LEFT OUTER JOIN
(
	SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
	INNER JOIN Topics ON Topics.topicid = Threads.topicid
	WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
) AS LastPosts
ON  
LastPosts.forumid = Topics.forumid AND
LastPosts.topicid = Topics.topicid
 

I hope this is what you are looking for. Good luck!

Cheers,

Florin


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
florinlabou
1/29/2008 11:42:15 AM

Help needed again from you all and florinalbou who provided the answer earlier. The requirements have changed that I have added a new table called Messages. The problem is described below.

 

Hi,

 Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005. 

I have four tables namely – Forums,Topics,Threads and Messages  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-

 

SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,

(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads

FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid

LEFT OUTER JOIN

(

                 SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics

             INNER JOIN Threads ON Topics.topicid = Threads.topicid

                 INNER JOIN Messages ON Threads.threadid=Messages.threadid

             WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)

) as LastPosts

ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid

 

Whose result set is as below:-

 

forumid

topicid name LastPostDate author NoOfThreads
1 1 Java Overall 2008-02-02 13:06:06.267 l@m.com 2
1 1 Java Overall 2008-01-27 14:46:41.000 c@b.com 2
1 2 JSP NULL NULL 0
1 3 EJB NULL NULL 0
1 4 Swings 2008-01-27 15:12:51.000 p@q.com 1
1 5 AWT NULL NULL 0
1 6 Web Services NULL NULL 0
1 7 JMS NULL NULL 0
1 8 XML,HTML NULL NULL 0
1 9 Javascript NULL NULL 0
2 10 Oracle NULL NULL 0
2 11 Sql Server NULL NULL 0
2 12 MySQL NULL NULL 0
3 13 CSS NULL NULL 0
3 14 FLASH/DHTLML NULL NULL 0
4 15 Best Practices NULL NULL 0
4 16 Longue NULL NULL 0
5 17 General NULL NULL 0
 

I want the result set as follows:-

 

forumid

topicid name LastPostDate author NoOfThreads
1 1 Java Overall 2008-02-02 13:06:06.267 l@m.com 2
1 2 JSP NULL NULL 0
1 3 EJB NULL NULL 0
1 4 Swings 2008-01-27 15:12:51.000 p@q.com 1
1 5 AWT NULL NULL 0
1 6 Web Services NULL NULL 0
1 7 JMS NULL NULL 0
1 8 XML,HTML NULL NULL 0
1 9 Javascript NULL NULL 0
2 10 Oracle NULL NULL 0
2 11 Sql Server NULL NULL 0
2 12 MySQL NULL NULL 0
3 13 CSS NULL NULL 0
3 14 FLASH/DHTLML NULL NULL 0
4 15 Best Practices NULL NULL 0
4 16 Longue NULL NULL 0
5 17 General NULL NULL 0
  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above. 

When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-

 

SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,

LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,

(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads

FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (

             SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads

             INNER JOIN Topics ON Topics.topicid = Threads.topicid

             WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)

             ) AS LastPosts

             ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid

 

Whose result set is as:-

 

ForumID

ForumName TopicID TopicName Author LastPost NoOfThreads
1 Developers 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2
1 Developers 2 JSP NULL NULL 0
1 Developers 3 EJB NULL NULL 0
1 Developers 4 Swings p@q.com 2008-01-27 15:12:51.000 1
1 Developers 5 AWT NULL NULL 0
1 Developers 6 Web Services NULL NULL 0
1 Developers 7 JMS NULL NULL 0
1 Developers 8 XML,HTML NULL NULL 0
1 Developers 9 Javascript NULL NULL 0
2 Database 10 Oracle NULL NULL 0
2 Database 11 Sql Server NULL NULL 0
2 Database 12 MySQL NULL NULL 0
3 Desginers 13 CSS NULL NULL 0
3 Desginers 14 FLASH/DHTLML NULL NULL 0
4 Architects 15 Best Practices NULL NULL 0
4 Architects 16 Longue NULL NULL 0
5 General 17 General NULL NULL 0
  

The scripts for creating the tables and inserting test data is as follows in an already created database:-

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Forums]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Topics]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Threads]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Messages]

GO

 

CREATE TABLE [dbo].[Forums] (

            [forumid] [int] IDENTITY (1, 1) NOT NULL ,

            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Topics] (

            [topicid] [int] IDENTITY (1, 1) NOT NULL ,

            [forumid] [int] NULL ,

            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Threads] (

            [threadid] [int] IDENTITY (1, 1) NOT NULL ,

            [topicid] [int] NOT NULL ,

            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [replies] [int] NOT NULL ,

            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [lastpostdate] [datetime] NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Messages] (

            [msgid] [int] IDENTITY (1, 1) NOT NULL ,

            [threadid] [int] NOT NULL ,

            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [date] [datetime] NULL ,

            [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Forums] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [forumid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Topics] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [topicid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Threads] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [threadid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Messages] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [msgid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Topics] ADD

             FOREIGN KEY

            (

                        [forumid]

            ) REFERENCES [dbo].[Forums] (

                        [forumid]

            )

GO

 

ALTER TABLE [dbo].[Threads] ADD

             FOREIGN KEY

            (

                        [topicid]

            ) REFERENCES [dbo].[Topics] (

                        [topicid]

            )

GO

 

ALTER TABLE [dbo].[Messages] ADD

             FOREIGN KEY

            (

                        [threadid]

            ) REFERENCES [dbo].[Threads] (

                        [threadid]

            )

GO

  

------------------------------------------------------

 

insert into forums(name,description) values('Developers','Developers Forum');

insert into forums(name,description) values('Database','Database Forum');

insert into forums(name,description) values('Desginers','Designers Forum');

insert into forums(name,description) values('Architects','Architects Forum');

insert into forums(name,description) values('General','General Forum');

 

insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');

insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');

insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');

insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');

insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');

insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');

insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');

insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');

insert into topics(forumid,name,description) values(1,'Javascript','Javascript');

insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');

insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');

insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');

insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');

insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');

insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');

insert into topics(forumid,name,description) values(4,'Longue','Longue');

insert into topics(forumid,name,description) values(5,'General','General Discussion');

 

insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');

insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');

insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');

 

insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');

insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');

insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');

insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');

insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');

insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');

 


-----------------------------------------------
Please click Mark as Answer if this helped.
0
aparnagarg
2/2/2008 8:33:48 AM

Help needed again from you all and florinalbou who provided the answer earlier. The requirements have changed that I have added a new table called Messages. The problem is described below.

 

Hi,

 Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005. 

I have four tables namely – Forums,Topics,Threads and Messages  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-

 

SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,

(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads

FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid

LEFT OUTER JOIN

(

                 SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics

             INNER JOIN Threads ON Topics.topicid = Threads.topicid

                 INNER JOIN Messages ON Threads.threadid=Messages.threadid

             WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)

) as LastPosts

ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid

 

Whose result set is as below:-

 
forumid topicid name LastPostDate author NoOfThreads
1 1 Java Overall 2008-02-02 13:06:06.267 l@m.com 2
1 1 Java Overall 2008-01-27 14:46:41.000 c@b.com 2
1 2 JSP NULL NULL 0
1 3 EJB NULL NULL 0
1 4 Swings 2008-01-27 15:12:51.000 p@q.com 1
1 5 AWT NULL NULL 0
1 6 Web Services NULL NULL 0
1 7 JMS NULL NULL 0
1 8 XML,HTML NULL NULL 0
1 9 Javascript NULL NULL 0
2 10 Oracle NULL NULL 0
2 11 Sql Server NULL NULL 0
2 12 MySQL NULL NULL 0
3 13 CSS NULL NULL 0
3 14 FLASH/DHTLML NULL NULL 0
4 15 Best Practices NULL NULL 0
4 16 Longue NULL NULL 0
5 17 General NULL NULL 0
 

I want the result set as follows:-

 
forumid topicid name LastPostDate author NoOfThreads
1 1 Java Overall 2008-02-02 13:06:06.267 l@m.com 2
1 2 JSP NULL NULL 0
1 3 EJB NULL NULL 0
1 4 Swings 2008-01-27 15:12:51.000 p@q.com 1
1 5 AWT NULL NULL 0
1 6 Web Services NULL NULL 0
1 7 JMS NULL NULL 0
1 8 XML,HTML NULL NULL 0
1 9 Javascript NULL NULL 0
2 10 Oracle NULL NULL 0
2 11 Sql Server NULL NULL 0
2 12 MySQL NULL NULL 0
3 13 CSS NULL NULL 0
3 14 FLASH/DHTLML NULL NULL 0
4 15 Best Practices NULL NULL 0
4 16 Longue NULL NULL 0
5 17 General NULL NULL 0
  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above. 

When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-

 

SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,

LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,

(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads

FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (

             SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads

             INNER JOIN Topics ON Topics.topicid = Threads.topicid

             WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)

             ) AS LastPosts

             ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid

 

Whose result set is as:-

 
ForumID ForumName TopicID TopicName Author LastPost NoOfThreads
1 Developers 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2
1 Developers 2 JSP NULL NULL 0
1 Developers 3 EJB NULL NULL 0
1 Developers 4 Swings p@q.com 2008-01-27 15:12:51.000 1
1 Developers 5 AWT NULL NULL 0
1 Developers 6 Web Services NULL NULL 0
1 Developers 7 JMS NULL NULL 0
1 Developers 8 XML,HTML NULL NULL 0
1 Developers 9 Javascript NULL NULL 0
2 Database 10 Oracle NULL NULL 0
2 Database 11 Sql Server NULL NULL 0
2 Database 12 MySQL NULL NULL 0
3 Desginers 13 CSS NULL NULL 0
3 Desginers 14 FLASH/DHTLML NULL NULL 0
4 Architects 15 Best Practices NULL NULL 0
4 Architects 16 Longue NULL NULL 0
5 General 17 General NULL NULL 0
  

The scripts for creating the tables and inserting test data is as follows in an already created database:-

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Forums]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Topics]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Threads]

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Messages]

GO

 

CREATE TABLE [dbo].[Forums] (

            [forumid] [int] IDENTITY (1, 1) NOT NULL ,

            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Topics] (

            [topicid] [int] IDENTITY (1, 1) NOT NULL ,

            [forumid] [int] NULL ,

            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Threads] (

            [threadid] [int] IDENTITY (1, 1) NOT NULL ,

            [topicid] [int] NOT NULL ,

            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [replies] [int] NOT NULL ,

            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [lastpostdate] [datetime] NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[Messages] (

            [msgid] [int] IDENTITY (1, 1) NOT NULL ,

            [threadid] [int] NOT NULL ,

            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [date] [datetime] NULL ,

            [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Forums] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [forumid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Topics] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [topicid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Threads] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [threadid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Messages] ADD

             PRIMARY KEY  CLUSTERED

            (

                        [msgid]

            )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[Topics] ADD

             FOREIGN KEY

            (

                        [forumid]

            ) REFERENCES [dbo].[Forums] (

                        [forumid]

            )

GO

 

ALTER TABLE [dbo].[Threads] ADD

             FOREIGN KEY

            (

                        [topicid]

            ) REFERENCES [dbo].[Topics] (

                        [topicid]

            )

GO

 

ALTER TABLE [dbo].[Messages] ADD

             FOREIGN KEY

            (

                        [threadid]

            ) REFERENCES [dbo].[Threads] (

                        [threadid]

            )

GO

  

------------------------------------------------------

 

insert into forums(name,description) values('Developers','Developers Forum');

insert into forums(name,description) values('Database','Database Forum');

insert into forums(name,description) values('Desginers','Designers Forum');

insert into forums(name,description) values('Architects','Architects Forum');

insert into forums(name,description) values('General','General Forum');

 

insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');

insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');

insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');

insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');

insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');

insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');

insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');

insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');

insert into topics(forumid,name,description) values(1,'Javascript','Javascript');

insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');

insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');

insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');

insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');

insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');

insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');

insert into topics(forumid,name,description) values(4,'Longue','Longue');

insert into topics(forumid,name,description) values(5,'General','General Discussion');

 

insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');

insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');

insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');

 

insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');

insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');

insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');

insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');

insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');

insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');

 


-----------------------------------------------
Please click Mark as Answer if this helped.
0
aparnagarg
2/2/2008 8:34:13 AM

Hi,

Here are the queries that are listing the information you are looking for:

 

-- SQL 2000
SELECT Topics.forumid AS ForumID, Forums.name AS ForumName, Topics.topicid as TopicID,Topics.name as TopicName,
LastPostPerTopic.Date AS LastPostDate, LastPostPerTopic.author AS Author,
(select count(threadid) from threads where Threads.topicid = Topics.topicid) as NoOfThreads 
FROM Forums 
JOIN Topics ON Forums.forumid = Topics.forumid 
LEFT OUTER JOIN (
	SELECT Threads.TopicID, Messages.Author, Messages.Date
	FROM Messages
	INNER JOIN Threads ON Threads.ThreadID = Messages.ThreadID
	WHERE Messages.Date = (SELECT MAX(LastMessages.Date) FROM Messages LastMessages
							INNER JOIN Threads LastPostThreads ON LastPostThreads.ThreadID = LastMessages.ThreadID
							WHERE LastPostThreads.TopicID = Threads.TopicID)
) 
AS LastPostPerTopic 
ON LastPostPerTopic.topicid = Topics.topicid
  

 

-- SQL 2005
WITH LastPostPerTopic (TopicID, Author, Date) AS
(

SELECT Threads.TopicID, Messages.Author, Messages.Date
FROM Messages
INNER JOIN Threads ON Threads.ThreadID = Messages.ThreadID
WHERE Messages.Date = (SELECT MAX(LastMessages.Date) FROM Messages LastMessages
						INNER JOIN Threads LastPostThreads ON LastPostThreads.ThreadID = LastMessages.ThreadID
						WHERE LastPostThreads.TopicID = Threads.TopicID)

)

SELECT Topics.forumid AS ForumID, Forums.name AS ForumName, Topics.topicid as TopicID,Topics.name as TopicName,
LastPostPerTopic.Date AS LastPostDate, LastPostPerTopic.author AS Author,
(select count(threadid) from threads where Threads.topicid = Topics.topicid) as NoOfThreads 
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid 
LEFT OUTER JOIN LastPostPerTopic ON LastPostPerTopic.TopicID = Topics.TopicID
;
 

I hope this will help.

Cheers,

Florin

P.S. Please not unmark answer as they were answering your initial question. When your problem got changed it doesn't mean that the initial answer was wrong. Smile 

 

 


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
florinlabou
2/2/2008 4:54:13 PM

Hi Florin,

Thanks a lot once again for the help.


-----------------------------------------------
Please click Mark as Answer if this helped.
0
aparnagarg
2/3/2008 12:44:17 PM

Hi Florin,

One issue with your query's result. If two messages for a thread contin the same date/time value, all such messages get displayed. I want only one such record. Please help.


-----------------------------------------------
Please click Mark as Answer if this helped.
0
aparnagarg
2/11/2008 7:28:08 PM

I'm not able to test this situation right now, but could you please try:

SELECT TOP 1 Threads.TopicID, Messages.Author, Messages.Date
FROM Messages
INNER JOIN Threads ON Threads.ThreadID = Messages.ThreadID
WHERE Messages.Date = (SELECT MAX(LastMessages.Date) FROM Messages LastMessages
INNER JOIN Threads LastPostThreads ON LastPostThreads.ThreadID = LastMessages.ThreadID
WHERE LastPostThreads.TopicID = Threads.TopicID)
 

Cheers,

Florin


Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
florinlabou
2/12/2008 7:22:46 AM
Reply:

Similar Artilces:

SP to perform query based on multiple rows from another query's result set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = ...

How to set DDL's selected value by datasource other than DDL's datasource
I have three TemplateFields in a Gridview. In each, the ItemTemplate has a label whose text value is bound to the GridView's DataSource. But in the EditItemTemplate the DropDownList is populated with a separate DataSource. This is a fairly common setup, so that wasn't too much to deal with. What I need to do is, when the GridView goes in to Edit mode and the DropDownList is populated and displayed I want the value that was in the bound label to be the default selected value in the DropDownList. As it currently is, the DropDownList renders with "Select..." as the defau...

Could not execute query against OLE DB provider 'MSIDXS' Error
I have created a stored procedure for searching particular keywords in a MS Word or PDF docs using Index Server and processes the results through the SQL Server. So, it links both Index Server and SQL Server When I execute this stored proc from my ASP.NET page i'm getting this error. Could not execute query against OLE DB provider 'MSIDXS' My Sql Connection object in my .net page is SqlConnection objConn = new SqlConnection("Server=MISC\\MISC;Database=sapresources;User ID=sap;Password=sapres;"); I tried different ways but not able to understand where the problem is but It works fine whe...

remove duplicate rows
hi In my table I have: KEY1 x x x x x KEY2 x x x x x KEY3 x x x x x and KEY4 y y y y y KEY5 y y y y y at the end I want KEY3 x x x x x KEY5 y y y y y since KEY3=MAX(KEY1, KEY2, KEY3) and KEY5=MAX(KEY4, KEY5) how to achieve that? thx! On May 2, 1:42=A0pm, melanie wrote: > hi > > In my table I have: > > KEY1 x x x x x > KEY2 x x x x x > KEY3 x x x x x > > and > > KEY4 y y y y y > > KEY5 y y y y y > > at the end I want > > KEY3 x x x x x > KEY5 y y y y y > > since KEY3=3DMAX(KEY1...

Combine SQL query result with XML query result
Hi all, Hopefully someone can help me with this: I know how to setup and run a query on an SQL db and I can probably work out how to query an XML file, but how do I take the results of each and combine them into one collection (i'm presuming a dataset)? Once i've created the combined collection, i'd want to order it by some universal identifier present in both datasources e.g. product ID. It's worth pointing out that the XML file would not be in the same format/use the same field names (or number of fields) as the SQL query, so I guess I might need to bind my query resu...

DNN2
Hi, Almost up to speed on creating PA's but I am a little confused on what to do with the data providers.... I'm getting a couple of errors and not sure if I'm looking in the right place. I followed the Announcements module and made the various amendments. Ok. My module is called "Posters" I created the user controls etc and outputed the dll file as: Flextex.Posters.dll Likewise I created a seperate project for the DAL and outputted it as: Flextex.PostersSqlDataProvider.dll Left the default namespace blank etc... and hand coded it as DotNetNuke. Manually adde...

What's the Access query equivalent of SQL's "CONVERT"?
I'm trying to use the following query in a new Access-based project. It works fine with SQL Server, but Access doesn't seem to like "CONVERT". Dim MyQuery as string = "SELECT * from Deadlines WHERE ([DueDate] > CONVERT(DATETIME, '" & CurrDate & "', 112)) ORDER BY DueDate ASC" Anyone have an equivalent statement for Access? I need to only pull those records where 'DueDate' is today or later. Thanks in advance.Stephen Here's the error I get: Server Error in '/' Application. ---------------------------------------------------...

Failed while instantiating provider object associated with Namespace = 'Exchange 2007 Provider' with CLSID = 'Microsoft.Provisioning.Providers.Exchange2007Provider.Provider' with Error Code='0x8007000
Hi, After a lot of troubleshooting and reinstallations we finally managed to have the full MPF and sample web online. Unfortunatly we get the following errror message when we try to add a SMTP domain to a new organization.  Failed while instantiating provider object associated with Namespace = 'Exchange 2007 Provider' with CLSID = 'Microsoft.Provisioning.Providers.Exchange2007Provider.Provider' with Error Code='0x80070002'  Please help...   Kind regards   T Hello, Can you create a trace of the request and send it? Regards, SamuelPS...

Return the results of a select query in a column of another select query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?http://w...

What's the best way to transfer the result set from a query into Excel?
I usually export the query using "Results To File" and save it as a csv.  I think open MS Excel, open the csv file from Excel and then use the delimited wizard.  Is there a quicker way with less steps? Anyone with Excel can open a standard CSV file and it will "just work".  No need to use the delimited wizard.  At least in more recent office versions.Darrell Norton, MVPDarrell Norton's BlogPlease mark this post as answered if it helped you! Check if this helps: http://weblogs.sqlteam.com/dinakar/archive/2007/04/02/60155.aspx***********************Dinakar NethiLife...

Login Fails with .net's membership provider & duplicates registration
Hello Guys, I just setup a .net membership provider with our website. Which also runs a forum software, both sites use .net's membership provider. PROBLEM: 1. If i login with the "SAME USERNAME AND PASSWORD" in the aspnet_membership table, It works with the forum software, but fails with the code below. However, when Logged in the forum software, the login name shows on my custom page. 2. If i register with my custom registration page, it duplicates the field that alread exists in the table, ie if i setup membership.createuser("test", "test@mydomain.com"...

Microsoft OLE DB Provider for SQL Server error '80004005'. SQL Server does not exist or access denied.
Hi,I have a asp web application, where i use sql server 2005 as backend. This application generates reports . Before i was using SQL Server 2000, now its migrated to 2005. When i generate report for small date range then it works fine. But when the date range is big then around half of the report is generated and the following error is thrown. Microsoft OLE DB Provider for SQL Server error '80004005' [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. ...out.asp, line 405  Am sure the webcode is working fine and am connecting to the Da...

Access to the ADO.net Managed Provider 'SqlClientFactory' was denied in the data source with ID 'SqlDataSource1' because of security settings.
I am getting "Access to the ADO.net Managed Provider 'SqlClientFactory' was denied in the data source with ID 'SqlDataSource1' because of security settings. " When I use database connection to my web applicaiton. Can you tell me what is the problem. I have posted in forum http://forums.asp.net/thread/1306653.aspx but they asked me to post here. I am recieving the same error.  Did you find a solution to this? I was recieving this error while developing webparts for WSS SP2 running on .NET 2.0. I created a custom trust file with a permissionset and codegroup for my assembly...

Novice DBA looking for Advice on Querying the result of a query of a results of a query of a....
Many thanks in advance! Here's an example of what I'm working on.  Let's say I have a row of data that is returned in a query.  The columns are First, Middle, and last.  We'll say the values in each column, respectively, are 1, 2, and 3. So I run my first query, something akin to SELECT MIDDLE WHERE MIDDLE = 2.  And we get that row.  Excellent. What I'm trying to do, is after that statement is executed, run another one on the webpage that looks like SELECT LAST WHERE LAST = [the value that was returned in the previous statment]. And the k...

Web resources about - How to remove partially duplicate rows from select query's result set (DB Schema provided and query provided). - asp.net.sql-datasource

Resources last updated: 12/19/2015 12:19:37 PM