ALMOST DONE, I THINK

Hello All Developers and SQL Gurus.

I am almost complete with my query thanks to everyone. This is what my final table structure looks like and along with my queries. The query below works fine, but only does 1/2 of what I want it to do. Basically right now it will give me all folders that have the same client id that was passed by the parameter with a parentid of a given parameter. What I also want to do is besides give me all folders, I also want to give me documents that have the same clientid but does not reside any folders thus having a folderid pf "0". Hopefully this makes since... So how can I modify my query to incorporate two functions? I was thinking that I create a temp table and insert my data into that table from both queries.

All I really want to receive back is folders and documents in different rows that have the same id and also that the folder has a parentid of Zero.

*************** QUERY ***************

select * from Client c

INNER JOIN Folder cf on c.clientid=cf.clientid LEFT OUTER JOIN foldernavigation f on cf.folderid=f.folderid LEFT OUTER JOIN clientdocuments cd on cf.FolderID=cd.FolderID

WHERE

c.clientid=@.CID and f.parentid=@.PID

*************** TABLE STRUCTURE ***************

TABLE 1: ClientDocuments

FIELDS:

DocID int primarykey

ClientID int

CreatedByUser nvarchar

CreatedDate datetime

Content image

ContentType nvarchar

ContentTypeImage nvarchar

ContentSize int

FriendlyName nvarchar

FolderID int

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

TABLE 2: Client

FIELDS:

ClientID int primarykey

FirstName nvarchar

LastName nvarchar

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

TABLE 3: Folder

FIELDS:

FolderID int primarykey

ClientID int

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

TABLE 4: FolderNavigation

FIELDS:

NavID int primarykey

FolderID int

ParentID int

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

Just add a search criteria to retrieve the documents that have the same clientid but does not reside any folders :

select * from Client c
INNER JOIN Folder cf on c.clientid=cf.clientid LEFT OUTER JOIN foldernavigation f on cf.folderid=f.folderid LEFT OUTER JOIN clientdocuments cd on cf.FolderID=cd.FolderID
WHEREc.clientid=@.CID
and (f.parentid=@.PID OR cd.FolderID=0)