Nested Queries - 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)

Nested Queries

Author

Topic

matthisco
Starting Member

48 Posts

Posted - 2010-04-28 : 10:50:09

I'm trying to create a list of categories and documents using 2 seperate tables.

Something that will look like this:

HTML Code:

<ul>
<li>documentCategoryName <ul>
<li>documenttitle</li><li>documenttitle</li><li>documenttitle</li>
</ul>
</li>
<li>documentCategoryName </li>
</ul>

Here are my 2 tables:

Documents table

documentID int Unchecked
documentName nvarchar(MAX) Checked
documentCat int Checked
documentDescription ntext Checked
documentType nvarchar(MAX) Checked
documentSiteID int Checked

Documentcategory table

documentCategoryId int Unchecked
documentCategoryName nvarchar(MAX) Checked

I'm using nested queries, like this:

SELECT * FROM documents LEFT JOIN documentcategory ON documents.documentcat = documentcategory.documentcategoryid WHERE documentsiteid = @siteid

Within the results set Im then using a nother query:

Select * From documents Where documentCat = @doccat And documentsiteid = @siteid

Is there another way to do this? Can I use the SQL Group BY instead of usng 2 querys?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:54:35

can i ask the need of nested query? isnt it already included as a part of first?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

matthisco
Starting Member

48 Posts

Posted - 2010-04-28 : 11:08:10

quote:
Originally posted by visakh16

can i ask the need of nested query? isnt it already included as a part of first?

Thanks for your reply.

It is all in the original query yes.

But I do not know how to loop through the document titles and have the category name at the top of each section.

Can this be done in sql somehow?

Thanks again for your reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:10:01

can you show the expected output with some sample data. lets see if it can be done in a single query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

matthisco
Starting Member

48 Posts

Posted - 2010-04-29 : 04:43:14

Thanks again for your reply. That would be great.

Here is my query so far:

SELECT * FROM documents LEFT JOIN documentcategory
ON documents.documentcat = documentcategory.documentcategoryid
WHERE documentsiteid = 0

I would like to get the following results:

doumentcategoryname - About Us
DocumentName - doccy3
DocumentName - doccy3
doumentcategoryname - Map of Medicine
DocumentName - doccy5

Here are my results:

documentid | documentname | documentcat | documentdescription | documenttype | documentsiteid | documentcategoryId | doumentcategoryname

3 doccy3 2 description application/msword 0 2 About Us
4 doccy4 2 description application/msword 0 2 About Us
5 doccy5 1 description application/msword 0 1 Map Of Medicine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 02:47:39

use UNPIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources