Query (Is this possible)? - 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 2000 Forums

Transact-SQL (2000)

Query (Is this possible)?

Author

Topic

Swoosh
Starting Member

6 Posts

Posted - 2004-02-09 : 18:34:59

I have a Notes table (ID, Text, LastModified)used to store notes
I have a Folders table (ID, Text, LastModified) used to store folders.
I also have FolderStorage table (ID, UID, FID), maps folders to users.
I also have a NoteStorage table (ID, UID, FID, NID) used to map notes to users/folders.

I want to produce a query that would select all folder Names and the number of notes stored in each folder for a given user.

Is this possible?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-10 : 00:23:19

It's definitely possible. A fairly simple INNER JOIN should do it:


DECLARE @UserID INT

SET @UserID = 1202

SELECT FID, COUNT(NID) FROM NoteStorage 
WHERE UID = @UserID
GROUP BY FID

The above code returns the FolderID and number of notes inside it for a particular user. To get the folder names use an INNER JOIN to the Folders table:


SELECT FID, [Text], COUNT(NID) AS NumberOfNotes 
FROM NoteStorage 
INNER JOIN Folders
ON NoteStorage.FID = Folders.[ID]
WHERE UID = @UserID
GROUP BY FID, [Text]

You should try to avoid using keywords such as text, id, etc for column or table names since they have to be "escaped" using brackets, which can be quite a pain if you have to do it over and over again

Read the Books Online for more information.

OS

Swoosh
Starting Member

6 Posts

Posted - 2004-02-10 : 07:56:28

Thanks! It works as expected...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources