Basic subquery question - SQL Server Forums

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)

Basic subquery question

Author

Topic

KevinAMac
Starting Member

2 Posts

Posted - 2008-11-03 : 16:21:47

Hello All,

My SQL syntax is a bit rusty so I was hoping you guys could help me out.

I have a main table, called Session, primary key SessionID, and another table called SessionFolder, which contains a foreign key to SessionID (basic 1:M relationship).

I need to list records from Session, along with counts of associated records from the SessionFolder table. I was thinking I would need to use a subquery like this:

Select
S.SessionID,
(Select Count(*) As TotalChildren From SessionFolder
Where SF.SessionID = S.SessionID)
From Session S
Left Outer Join SessionFolder SF
Where SF.SessionID = S.SessionID

So, you can see that for each record in the Session table, I just want a count of how many child records there are in the SessionFolder table. Am I on the right track?

Thanks,
Kevin

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-03 : 16:33:28

quote:
Originally posted by KevinAMac

Hello All,

My SQL syntax is a bit rusty so I was hoping you guys could help me out.

I have a main table, called Session, primary key SessionID, and another table called SessionFolder, which contains a foreign key to SessionID (basic 1:M relationship).

I need to list records from Session, along with counts of associated records from the SessionFolder table. I was thinking I would need to use a subquery like this:

Select S.SessionID,Count(*) As TotalChildren
From Session S inner join SessionFolder SF
on S.SessionID = SF.SessionID
Group by S.SessionID

So, you can see that for each record in the Session table, I just want a count of how many child records there are in the SessionFolder table. Am I on the right track?

Thanks,
Kevin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-03 : 16:33:46

Try this:


select s.SessionID, s.somecol1, s.somecol2, s.somecol3, isnull(t.TotalChildren,0) as TotalChildren
from Session s
left join
(select SessionId, count(*) as TotalChildren from SessionFolder group by SessionId)t
on (t.SessionId = s.SessionID)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

KevinAMac
Starting Member

2 Posts

Posted - 2008-11-03 : 17:01:31

SoDeep,

Thanks for the speedy reply. It has been frustrating getting back into SQL coding again...

I used your example and it worked perfectly --- thanks!

I needed two different counts from the SessionFolder table so I wrote it using correlated subqueries like this:

SELECT S.SessionID,
(SELECT COUNT(*) FROM Folder WHERE Folder.SessionID=S.SessionID) AS TotalFolders,
(SELECT COUNT(*) FROM Folder WHERE Folder.SessionID=S.SessionID AND Folder.StatusID=1) AS Status1Folders
FROM [Session] AS S
ORDER BY S.SessionID;

This seems to work correctly, and I think I had to go to a correlated subquery in order to get the two counts from the Folder (SessionFolder) table...

quote:
Originally posted by sodeep

quote:
Originally posted by KevinAMac

Hello All,

My SQL syntax is a bit rusty so I was hoping you guys could help me out.

I have a main table, called Session, primary key SessionID, and another table called SessionFolder, which contains a foreign key to SessionID (basic 1:M relationship).

I need to list records from Session, along with counts of associated records from the SessionFolder table. I was thinking I would need to use a subquery like this:

Select S.SessionID,Count(*) As TotalChildren
From Session S inner join SessionFolder SF
on S.SessionID = SF.SessionID
Group by S.SessionID

So, you can see that for each record in the Session table, I just want a count of how many child records there are in the SessionFolder table. Am I on the right track?

Thanks,
Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 17:23:21

Learn JOINs.

SELECT		s.SessionID,
		COUNT(f.SessionID) AS totalFolders,
		SUM(CASE WHEN f.StatusID = 1 THEN 1 ELSE 0 END) AS statusFolders
FROM		[Session] AS s
LEFT JOIn	Folder AS f ON f.SessionID = s.SessionID
ORDER BY	s.SessionID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources