Can I put these subqueries in one SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can I put these subqueries in one SQL statement

Author

Topic

Speckled
Starting Member

13 Posts

Posted - 2009-06-24 : 10:30:55

Hi,

I have the following:


	SELECT	fs.SectionTitle,
			fs.SectionBody,
			fs.ForumSectionID AS FSID,
			(SELECT COUNT(*) FROM ForumThreads ft WHERE ft.ForumSectionID = fs.ForumSectionID) AS NoOfThreads,
			(SELECT COUNT(*) - 1 FROM ForumPosts fp INNER JOIN ForumThreads ft ON ft.ForumSectionID = fs.ForumSectionID AND ft.ThreadID = fp.ThreadID) AS NoOfPosts,
			(SELECT TOP 1 ft.Title FROM ForumThreads ft INNER JOIN ForumPosts fp ON ft.ThreadID = fp.ThreadID WHERE ft.ForumSectionID = fs.ForumSectionID ORDER BY fp.PostCreated DESC) AS NewestThread,
			(SELECT TOP 1 ft.ThreadID FROM ForumThreads ft INNER JOIN ForumPosts fp ON ft.ThreadID = fp.ThreadID WHERE ft.ForumSectionID = fs.ForumSectionID ORDER BY fp.PostCreated DESC) AS NewestThreadID
	FROM ForumSections fs

In bold, is the same SQL query. The same query returns one row, and I want two columns from it. The thread title and threadID. Do I have to write two different subqueries to get the two different fields!?

Thanks,
Ricky :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-24 : 11:11:41

If you can provide some sample data and expected output this could probably be done with some joins:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

but, here is one quick and dirty way to do it with the APPLY operator:

SELECT	
	fs.SectionTitle,
	fs.SectionBody,
	fs.ForumSectionID AS FSID,
	(SELECT COUNT(*) FROM ForumThreads ft WHERE ft.ForumSectionID = fs.ForumSectionID) AS NoOfThreads,
	(SELECT COUNT(*) - 1 FROM ForumPosts fp INNER JOIN ForumThreads ft ON ft.ForumSectionID = fs.ForumSectionID AND ft.ThreadID = fp.ThreadID) AS NoOfPosts,
	T.Title AS NewestThread,
	T.ThreadID AS NewestThreadID
FROM 
	ForumSections fs
CROSS APPLY
	(
		SELECT TOP 1 
			ft.Title,
			ft.ThreadID
		FROM 
			ForumThreads ft 
		INNER JOIN 
			ForumPosts fp 
			ON ft.ThreadID = fp.ThreadID 
		WHERE 
			ft.ForumSectionID = fs.ForumSectionID 
		ORDER BY 
			fp.PostCreated DESC
	) AS T

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-06-24 : 11:41:31

Try with the 'with' statement. (This statement changed my life. maybe it will change yours?)

http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm

If you get stuck, let me know...

Speckled
Starting Member

13 Posts

Posted - 2009-06-24 : 12:06:41

OK, hopefully this helps. I have three tables that this links too. It's basically a forum, but it has three sections.

One, is the forum sections:

CREATE TABLE [dbo].[ForumSections](
[ForumSectionID] [int] IDENTITY(1,1) NOT NULL,
[SectionTitle] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[SectionBody] [text] COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_ForumSections] PRIMARY KEY CLUSTERED
(
[ForumSectionID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Two is the forum threads:

CREATE TABLE [dbo].[ForumThreads](
[ThreadID] [int] IDENTITY(1,1) NOT NULL,
[ForumSectionID] [int] NULL,
[UserID] [int] NULL,
[Title] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[UserViews] [int] NULL,
[Locked] [bit] NULL,
[Sticky] [bit] NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_ForumThreads] PRIMARY KEY CLUSTERED
(
[ThreadID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ForumThreads] WITH CHECK ADD CONSTRAINT [FK_ForumThreads_ForumThreads] FOREIGN KEY([ThreadID])
REFERENCES [dbo].[ForumThreads] ([ThreadID])
GO
ALTER TABLE [dbo].[ForumThreads] CHECK CONSTRAINT [FK_ForumThreads_ForumThreads]

And the forum posts:

CREATE TABLE [dbo].[ForumPosts](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[ThreadID] [int] NULL,
[UserID] [int] NULL,
[PostMessage] [text] COLLATE Latin1_General_CI_AS NULL,
[PostCreated] [datetime] NULL,
CONSTRAINT [PK_ForumPosts] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[ForumPosts] WITH CHECK ADD CONSTRAINT [FK_ForumPosts_ForumPosts] FOREIGN KEY([PostID])
REFERENCES [dbo].[ForumPosts] ([PostID])
GO
ALTER TABLE [dbo].[ForumPosts] CHECK CONSTRAINT [FK_ForumPosts_ForumPosts]
GO
ALTER TABLE [dbo].[ForumPosts] WITH CHECK ADD CONSTRAINT [FK_ForumPosts_ForumThreads] FOREIGN KEY([ThreadID])
REFERENCES [dbo].[ForumThreads] ([ThreadID])
GO
ALTER TABLE [dbo].[ForumPosts] CHECK CONSTRAINT [FK_ForumPosts_ForumThreads]

So, if we have a few sections:

INSERT INTO ForumSections (SectionTitle, SectionBody) VALUES ('This is section one', 'Body of section one')
INSERT INTO ForumSections (SectionTitle, SectionBody) VALUES ('This is section two', 'Body of section two')

Now add some threads:

INSERT INTO ForumThreads (ForumSectionID, Title) VALUES (1, 'Thread one for section one')
INSERT INTO ForumThreads (ForumSectionID, Title) VALUES (1, 'Thread one for section two')
INSERT INTO ForumThreads (ForumSectionID, Title) VALUES (2, 'Thread one for section one')
INSERT INTO ForumThreads (ForumSectionID, Title) VALUES (2, 'Thread one for section two')

And some posts:

INSERT INTO ForumPosts (ThreadID, PostMessage) VALUES (1, 'post one for thread one')
INSERT INTO ForumPosts (ThreadID, PostMessage) VALUES (1, 'post two for thread one')
INSERT INTO ForumPosts (ThreadID, PostMessage) VALUES (1, 'post three for thread one')
INSERT INTO ForumPosts (ThreadID, PostMessage) VALUES (2, 'post one for thread two')
INSERT INTO ForumPosts (ThreadID, PostMessage) VALUES (2, 'post two for thread two')
INSERT INTO ForumPosts (ThreadID, PostMessage) VALUES (2, 'post three for thread two')

Right!

So now back to these queries:

SELECT	fs.SectionTitle,
			fs.SectionBody,
			fs.ForumSectionID AS FSID,
			(SELECT COUNT(*) FROM ForumThreads ft WHERE ft.ForumSectionID = fs.ForumSectionID) AS NoOfThreads,
			(SELECT COUNT(*) - 1 FROM ForumPosts fp INNER JOIN ForumThreads ft ON ft.ForumSectionID = fs.ForumSectionID AND ft.ThreadID = fp.ThreadID) AS NoOfPosts,
			(SELECT TOP 1 ft.Title FROM ForumThreads ft INNER JOIN ForumPosts fp ON ft.ThreadID = fp.ThreadID WHERE ft.ForumSectionID = fs.ForumSectionID ORDER BY fp.PostCreated DESC) AS NewestThread,
			(SELECT TOP 1 ft.ThreadID FROM ForumThreads ft INNER JOIN ForumPosts fp ON ft.ThreadID = fp.ThreadID WHERE ft.ForumSectionID = fs.ForumSectionID ORDER BY fp.PostCreated DESC) AS NewestThreadID

I currently get the correct results. The most recent thread title and it's relevant ID.

But, with the last two subqueries, all I do is change one field. Can I not do this anyother way?

When I tried joining I got all the threads and all the posts listed - where as I just want the forum sections and the newest threadID/title.

Hope that makes some sense, at least. And thanks for replies so far. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:11:49

[code]SELECT fs.SectionTitle,
fs.SectionBody,
fs.ForumSectionID AS FSID,
t1.NoOfThreads,
t2.NoOfPosts,
t3.NewestThread,
t3.NewestThreadID
FROM [dbo].[ForumSections] fs
OUTER APPLY(SELECT COUNT(*) AS NoOfThreads FROM ForumThreads ft WHERE ft.ForumSectionID = fs.ForumSectionID) t1
OUTER APPLY (SELECT COUNT(*) - 1 AS NoOfPosts FROM ForumPosts fp INNER JOIN ForumThreads ft ON ft.ForumSectionID = fs.ForumSectionID AND ft.ThreadID = fp.ThreadID) t2
OUTER APPLY (SELECT TOP 1 ft.Title AS NewestThread,ft.ThreadID AS NewestThreadID FROM ForumThreads ft INNER JOIN ForumPosts fp ON ft.ThreadID = fp.ThreadID WHERE ft.ForumSectionID = fs.ForumSectionID ORDER BY fp.PostCreated DESC) t3
[/code]

Speckled
Starting Member

13 Posts

Posted - 2009-06-24 : 13:33:46

Yes, that worked. :)

Thankyou for all your efforts.

Time for me to learn OUTER APPLY.

Cheers!
Ricky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:42:47

welcome

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 23:20:44

We have a "Dr. Cross Apply / Outer Apply" here