Hurry! Query needs help

Hello Fellow Developers,

I am very new to SQL and ASP.net and in the learning process. I have to do a small web application that handles documents stored on a sql database. The requirements of the functionality of the application are below. I am just having trouble setting my query to work correctly.

Problem:

I want to be able to pull documents and folders that have the same ClientID. However, here is the catch, the query should take two inputs - ClientID and ParentID (which is an ID representing FolderID from the Folder Table; the document/folder does not belong to another folder then the system will enter ZERO as the ParentID). I hope this is clear? Can this even be done?

Any help is greatly appreciated, as life is a learning experience.

Database Structure:

Table Name: Client

Fields:

ClientID primary key autonumber

Name nvarchar 255

Table Name: ClientDocs

Fields:

DocID primary key autonumber

Document image (the actually file in bytes)

FriendlyName nvarchar 255

ClientID foreign key -> [Table Client]

ClientFolderID foreign key -> [Table ClientFolders]

Table Name: Folders

Fields:

FolderID primarykey autonumber

FolderName nvarchar 255

Table Name: ClientFolder

Fields:

ClientFolderID primarykey autonumber

FolderID foreign key -> [Table Folders]

ParentID int

ClientID foreign key -> [Table Clients]

saeapu:

Problem:

I want to be able to pull documents and folders that have the same ClientID. However, here is the catch, the query should take two inputs - ClientID and ParentID (which is an ID representing FolderID from the Folder Table; the document/folder does not belong to another folder then the system will enter ZERO as the ParentID).

What's the exact thing will the query do? To me it seems an insert to some table (may be ClientFolders?). Then where do the 2 input valuesClientID and ParentIDcome from? You mentioned when will the ParentID be set to 0, so what's the value of ParentID if it dose not satisfy the condition to be 0? And how do you want to deal with FolderID in your query, as there is a FolderID column in ClientFolders?

|||Not sure if i understand the question, is it just to return the data using a sql statement or stored procedure?

If it is, using a stored procedure you could:

Declare 2 parameters, @.ClientId and @.ParentId.

In the Where clause of your statement have something like

WHERE
ClientId = @.ClientId
AND (ParentId = @.ParentId
OR @.ParentId = 0)|||

Iori_Jay:

What's the exact thing will the query do? To me it seems an insert to some table (may be ClientFolders?). Then where do the 2 input values ClientID and ParentID come from? You mentioned when will the ParentID be set to 0, so what's the value of ParentID if it dose not satisfy the condition to be 0? And how do you want to deal with FolderID in your query, as there is a FolderID column in ClientFolders?

Thanks Lori_jay for the input and questions. Hopefully below I can explain the problem better.

Basically I am creating a small web application. Currently what my app can do is upload files to a sql database and associate the file(s) to a clientID. So there is 1 Client to many documents.

This works fine and dandy, but what I am trying to do is give the capability to create folders in the application. And let users insert a document into a particular folder which that folder is associated to a client and those documents inside the folder is also associated to the same ClientID. But they do not have to put the document inside a folder. They can just upload the document and just associate it to the ClientID. The reason I did parentID is that if they wanted to create a folder inside another folder. So the queries I will be needing help with is the 'Select' query to retrieve all documents and folders that have a two paremetersPartentID andClientID.

ParentID = a integer representing the folder that document/folder belongs too. If the folder /document is at the root level of the client then the value will be "0".

ClientID = a integer representing a Client.

Maybe my table structures are incorrect. What do you think?

Here is a small tree like structure I have in mind.

ClientID

|

|__ Folders

| |

| |__________Folder

| |

| |__Documents

|

|__ Documents

|||

DrGonzo:

Not sure if i understand the question, is it just to return the data using a sql statement or stored procedure?

If it is, using a stored procedure you could:

Declare 2 parameters, @.ClientId and @.ParentId.

In the Where clause of your statement have something like

WHERE
ClientId = @.ClientId
AND (ParentId = @.ParentId
OR @.ParentId = 0)

Hey Thanks DrGonzo, I have tried that already and no luck Can you give me an example on how you would use the tables I have given above and JOIN them to make this query work.

Thanks.

|||

saeapu:

So the queries I will be needing help with is the 'Select' query to retrieve all documents and folders that have a two paremetersPartentID andClientID.

ParentID = a integer representing the folder that document/folder belongs too. If the folder /document is at the root level of the client then the value will be "0".

ClientID = a integer representing a Client.

Then how do you konw the folder/document is at the root level? Can I say a row in ClientFolders table which has 0 for ParentID column represents a top level entity? If so we do not need to consider the @.ParentID with value 0 as a particular issue, we can just treat @.ParentID as it is other value (1,2,3...ect.). The top level entity logic is implemented when you insert the row into ClientFolders table, right? Let's try such query:

declare @.ClientID int,@.ParentID int
select @.ClientID=1,@.ParentID=1
select c.ClientID,c.Name,cf.ClientFolderID,cf.ParentID,
f.FolderID,f.FolderName,cd.FriendlyName
from ClientFolders cf join Folders f on cf.FolderID = f.FolderID
join ClientDocs cd on cf.ClientFolderID=cd.ClientFolderID
join Clients c on cf.ClientID = c.ClientID
wherecf.ClientID=@.ClientID
andcf.ParentID=@.ParentID