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)

Allow Developer to Design tables, but not be owner?

I have some developers that I need to be able to do anything to the Database
except look in a few tables in that database.
I have a DenyHR Role that denies Access to the HR Tables, though if I give
them dbOwner that seems to defeat the purpose.
They need to still create and design existing tables. How can I accomplish
this?
Thank you,
Scott<-It sounds like you need to put them in the db_ddladmin role.
Alter table isn't transferable so you'd likely need to use
this role which is more restrictive than db_owner.
-Sue
On Thu, 7 Sep 2006 11:45:57 -0700, "Scott Townsend"
<scooter@.nospam.postalias> wrote:

>I have some developers that I need to be able to do anything to the Databas
e
>except look in a few tables in that database.
>I have a DenyHR Role that denies Access to the HR Tables, though if I give
>them dbOwner that seems to defeat the purpose.
>They need to still create and design existing tables. How can I accomplish
>this?
>Thank you,
> Scott<-
>|||Using SQL 2000, there is no easy solution at hand. In some situations, the
'sensitive' data tables are put into a different database for reasons like
this one. Different security needs.
Using SQL 2005, you may benefit from investigations schemas. See 'Using
Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Scott Townsend" <scooter@.nospam.postalias> wrote in message
news:Ojh7b3q0GHA.4772@.TK2MSFTNGP03.phx.gbl...
>I have some developers that I need to be able to do anything to the
>Database except look in a few tables in that database.
> I have a DenyHR Role that denies Access to the HR Tables, though if I give
> them dbOwner that seems to defeat the purpose.
> They need to still create and design existing tables. How can I accomplish
> this?
> Thank you,
> Scott<-
>