ALMOST DONE, I THINK?

microsoft.public.sqlserver.programming

14 Jul 2006 1:03 AM

manmit.walia

Hello All Developers and SQL Gurus.
I am almost complete with my query thanks to Jeff Tuner a fellow Google
Groups user. This is what my final table structure looks like and along
with my queries. The query below works fine 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.

CAN THIS BE DONE!

*************** 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
---------------------------------------

14 Jul 2006 2:54 AM

Uri Dimant

Hi
Without seeing an actual data ana an expected result I can only guess

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

<manmit.wa***@gmail.com> wrote in message
Show quote

news:1152839034.251537.102030@b28g2000cwb.googlegroups.com...
> Hello All Developers and SQL Gurus.
> I am almost complete with my query thanks to Jeff Tuner a fellow Google
> Groups user. This is what my final table structure looks like and along
> with my queries. The query below works fine 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.
>
> CAN THIS BE DONE!
>
>
> *************** 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
> ---------------------------------------
>