SQL Newbie with a join question

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

General SQL Server Forums

New to SQL Server Programming

SQL Newbie with a join question

Author

Topic

brettdmd
Starting Member

12 Posts

Posted - 2010-03-10 : 10:44:36

Hello,

I was referred to this board by a colleague, but am new to SQL and learning as I go. Hopefully the following will make sense.

Was asked to produce a SQL report from an application database. The report will outline any differences between two container objects, and their contents in the application.

Here's what I'm working with;

dbo.project
projid (PK, float)
projpid (float)
projname (varchar)
projown (varchar)
projtree (int)

dbo.project_content
sid (PK, int)
projid (FK, float)
item_id (float) 
projtree (int)

dbo.document
docnum (PK, int)
docname (varchar)
docowner (varchar)
editdate (datetime)
createdate (datetime)

Now, the nature of this application uses a hierarchy similar to Windows explorer with root level folders, subfolders and documents. Folder (root and sub) objects are stored in

dbo.project

, with documents being the smallest object. Folder contents are listed in

dbo.project_content

, and the attributes for documents listed in - you guessed it -

dbo.document

.

The format of this report should highlight the root level folders and the parent-child-grandchild relationship shared with subfolders and subfolder contents, sort of like this;

And so on.

I've been able to assemble a query that "sort of" gets me where I want to be, but I fear that I've somehow joined a parent folder as a root-level folder - which may or may not always be the case. Here is the query;

select d.projname as [Root], c.projname as [SubFolder], a.item_id as [DocumentNum], b.docname as [Document Name],  b.createdate as [Create Date], b.editdate as [Edit Date]
from dbo.project_content a
inner join dbo.document b
on a.item_id = b.docnum
inner join dbo.project c
on a.projid = c.projid
inner join dbo.project d
on c.prjopid = d.prjoid

The reason I joined dbo.project twice is to hopefully capture the root & subfolder relationship. This may or may not be the best way to do this?

I'm a little unsure what to expect from this forum, but hopefully this info is clear to all of you and can help guide me down this path.

Cheers,
Brett D.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 11:56:58

so is your requirement to traverse through the hierarchy until you reach the child level? also are you using sql 2005?

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-10 : 11:59:42

What version of SQL are you using? If you are using SQL 2005 or later you should take a look at Recursive CTE [SQL Server] in Books Online (BOL). That should help get you going as to how to select/traverse a hierarchical data structure.

brettdmd
Starting Member

12 Posts

Posted - 2010-03-10 : 12:01:02

quote:
Originally posted by visakh16

so is your requirement to traverse through the hierarchy until you reach the child level? also are you using sql 2005?

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

Yes, this is my intent. Yes, I am using SQL 2005....and reading up on Recursive CTE as we speak!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:03:36

quote:
Originally posted by brettdmd

quote:
Originally posted by visakh16

so is your requirement to traverse through the hierarchy until you reach the child level? also are you using sql 2005?

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

Yes, this is my intent. Yes, I am using SQL 2005....and reading up on Recursive CTE as we speak!

cool see below

http://msdn.microsoft.com/en-us/library/ms186243.aspx

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-10 : 12:11:33

quote:
Originally posted by visakh16

quote:
Originally posted by brettdmd

quote:
Originally posted by visakh16

so is your requirement to traverse through the hierarchy until you reach the child level? also are you using sql 2005?

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

Yes, this is my intent. Yes, I am using SQL 2005....and reading up on Recursive CTE as we speak!

cool see below

http://msdn.microsoft.com/en-us/library/ms186243.aspx

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

As interesting as this is, I'm only on the 2nd paragraph and already waaaaaaaaay over my head.

Obviously this will require more than some simple changes to my existing query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:19:24

yup..it will

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-10 : 12:53:41

So please correct me if I don't understand this correctly. Recursive queries work using the following logic;

WITH cte_name AS
 ( Cte_query_1 -
   UNION ALL
   Cte_query_2 )
Statement that uses the above CTE

So my first query would be to establish the root level folder, and second query to identify any subfolders therein. Correct?

And the statement calling my recursive query then lists the documents and document details?

Sorry for the drawn out post...this is all very complex for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:03:11

quote:
Originally posted by brettdmd

So please correct me if I don't understand this correctly. Recursive queries work using the following logic;

WITH cte_name AS
( Cte_query_1 -
UNION ALL
Cte_query_2 )
Statement that uses the above CTE

So my first query would be to establish the root level folder, and second query to identify any subfolders therein. Correct?

And the statement calling my recursive query then lists the documents and document details?

Sorry for the drawn out post...this is all very complex for me.

yup. Cte_query_1 is called anchor member and Cte_query_2 recursive member

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-10 : 13:55:55

OK so I've run into a stumbling block.

Does this method require that all fields queried in your anchor member must originate in the same table?

i.e.- is it possible to use join statements in your anchor query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:56:34

yup..its possible to use joins

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-11 : 08:46:13

quote:
Originally posted by visakh16

yup..its possible to use joins

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

This is starting to become clear, but something tells me I've got a ways to go!! Can anyone please suggest a shortcut?

Here's what I've got so far;


with projectdocs as
(
select a.projname as [Root],
	   b.projname as [SubFolder],
	   c.item_id as [Docnum],
	   d.docname as [Document Name]
from dbo.document d
	   inner join dbo.project_content c
	   on d.docnum = c.item_id
	   inner join dbo.project b
	   on b.projid = c.projid
	   inner join dbo.project a
	   on a.tree_id = c.tree_id
union all
select e.prj_name as [Root],
	   f.projname as [SubFolder],
	   g.item_id as [Docnum],
	   h.docname as [Document Name]
from dbo.document h
	   inner join dbo.project_content g
	   on h.docnum = g.item_id
	   inner join dbo.project f
	   on f.projid = g.projid
	   inner join dbo.project e
	   on e.tree_id = g.tree_id
)
select * from projectdocs
where root = '36076'

Which actually returns 0 results. *sigh*

Maybe I have not organized my anchor member and recursive member properly??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:10:18

may be you could give some data to make your scenario clear. I cant see what field represents the hierarchial relationship of project?

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-11 : 11:27:19

quote:
Originally posted by visakh16

may be you could give some data to make your scenario clear. I cant see what field represents the hierarchial relationship of project?

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

No problem!


select top 10 * from dbo.project
PROJID	PROJPID	PROJNAME	PROJOWNER	PROJTREE
1	NULL	My Favorites	ICATANOI	1
2	NULL	My Matters	NRTWEBADMIN	2
510196	2	test_WMV	NRTWEBADMIN	2
440317	2	CORPORATE	NRTWEBADMIN	2
440315	2	ANTITRUST and COMPETITION	NRTWEBADMIN	2
440314	2	ALL	NRTWEBADMIN	2
310042	2	Branta Destruction / Documents	NRTWEBADMIN	2
266669	2	WS_Admin	NRTWEBADMIN	2
266664	2	2007 Partner Review	NRTWEBADMIN	2
266662	2	2006 Partner Review	NRTWEBADMIN	2


select top 10 * from dbo.project_content
SID	PRJ_ID	ITEM_ID
10	95	362657
11	95	410934
15	160	450364
18	160	457037
17	160	457554
16	160	466084
15240	186	4016088
502473	190	498726
28338	190	4040689
131196	190	4148320

select top 10 * from dbo.document
DOCNAME	DOCNUM	DOCOWNER	EDITDATE	CREATEDATE
Document1	7635	JSMITH	25/09/02	10:14:38 AM
Document2	7749	ABARNES	26/04/04	10:49:31 AM
Document3	9271	BRETTD	10/02/04	11:12:55 AM
Document4	9554	OJSIMPSON	31/10/02	01:07:45 PM
Document5	15272	CMARX	10/02/04	11:10:41 AM
Document6	15507	DJETER	07/03/03	 6:41:08 PM
Document7	17375	MMCGWUIRE	18/12/02	 8:55:07 PM
Document8	17616	BHANA	04/03/04	07:25:22 PM
Document9	23112	CMITH	18/02/04	 2:34:21 PM
Document10	26125	TCRUZ	22/01/04	 3:15:08 PM

Part of the problem (i think?!) is that both root and subfolders are detailed in the project table. This is why the table is joined twice...but I've become unsure if this is indeed required with a recursive query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:36:43

how are project and project_content related?

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-11 : 11:45:55

quote:
Originally posted by visakh16

how are project and project_content related?

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

Project_content itemizes the documents stored in the container objects detailed in Project.

Project_content.Prj_ID is the FK to Project.Prj_ID (which is the PK).

brettdmd
Starting Member

12 Posts

Posted - 2010-03-11 : 22:31:52

quote:
Originally posted by brettdmd

quote:
Originally posted by visakh16

how are project and project_content related?

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

Project_content itemizes the documents stored in the container objects detailed in Project.

Project_content.Prj_ID is the FK to Project.Prj_ID (which is the PK).

Better sample data set below. This is the data for 1 root level folder, the subfolders for that item (same project table), and some documents in those 1st level subfolders.

select * from dbo.project where projname = 'SFDS'

ProjID	ProjPID	ProjName	ProjOwner	ProjTree
370606	NULL	SFDS		DMA	 	370606

select * dbo.project where projpid = '370606.0'


ProjID	        ProjPID	        ProjName	ProjOwner	ProjTree
586655.0	370606.0	Manage		WSA		370606
493205.0	370606.0	test		TSH		370606
448003.0	370606.0	*TrashCan	TNO		370606
437203.0	370606.0	Letters		WSA		370606
432986.0	370606.0	*SFDS		DMA		370606
425843.0	370606.0	_Index		DMA		370606
420140.0	370606.0	*Removed	WSA		370606
402924.0	370606.0	Confid		WSA		370606
376760.0	370606.0	*Duplicate	DMA		370606
374135.0	370606.0	Technology	DMA		370606

select * from dbo.project_content where projtree = '370606'

SID	ProjID	Item_ID	   ProjTree
370606  64364.0 4886791.0  370606

select * from dbo.document where docnum = '4886791'

DocNum	    DocName	DocOwner	EditDate	        CreateDate
4886791.0   0042.tif	SBL	        2003-07-28 14:07:30.000	2003-12-16 17:29:36.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 12:36:07

[code]
;With CTE(projid,
projpid,
ProjName,
ProjOwner,
DocName)
AS
(
SELECT p.projid,
p.projpid,
p.ProjName,
p.ProjOwner,
d.DocName
FROM dbo.project p
INNER JOIN dbo.project_content pc
ON pc.ProjTree = p.ProjTree
INNER JOIN dbo.document d
ON d.DocNum = pc.Item_ID
WHERE p.ProjPID IS NULL

UNION ALL

SELECT p.projid,
p.projpid,
p.ProjName,
p.ProjOwner,
d.DocName
FROM dbo.project p
INNER JOIN dbo.project_content pc
ON pc.ProjTree = p.ProjTree
INNER JOIN dbo.document d
ON d.DocNum = pc.Item_ID
INNER JOIN CTE c
ON c.projid= p.projpid
)

SELECT projid,
projpid,
ProjName,
ProjOwner,
DocName
FROM CTE

OPTION (MAXRECURSION 0)
[/code]

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-12 : 18:15:43

quote:

Originally posted by visakh16


;With CTE(projid,
projpid,  
ProjName,
ProjOwner,
DocName)
AS
(
SELECT p.projid,
p.projpid,  
p.ProjName,
p.ProjOwner,
d.DocName
FROM dbo.project p
INNER JOIN dbo.project_content pc
ON pc.ProjTree = p.ProjTree
INNER JOIN dbo.document d
ON d.DocNum = pc.Item_ID
WHERE p.ProjPID IS NULL

UNION ALL

SELECT p.projid,
p.projpid,  
p.ProjName,
p.ProjOwner,
d.DocName
FROM dbo.project p
INNER JOIN dbo.project_content pc
ON pc.ProjTree = p.ProjTree
INNER JOIN dbo.document d
ON d.DocNum = pc.Item_ID
INNER JOIN CTE c
ON c.projid= p.projpid  
)

SELECT projid,
projpid,  
ProjName,
ProjOwner,
DocName
FROM CTE

OPTION (MAXRECURSION 0)

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

Thank you for all your help Visakh, this is excellent.

This statement does return the proper root folder and grandchild objects, it did not return the child object.

Why would it skip 1 level in the hierarchy?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 00:58:51

why it didnt return child record? it would return child provided its correctly linked by projpid field in table

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

brettdmd
Starting Member

12 Posts

Posted - 2010-03-14 : 15:48:45

quote:
Originally posted by visakh16

why it didnt return child record? it would return child provided its correctly linked by projpid field in table

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

I am sorry, but I am still struggling with this.

Here is a query which returns the correct data, in the expected format - but only for those records where sub.projpid = root.projid;


select root.projname as [Root Folder], 
sub.projname as [Sub Folder], 
docs.docname as [Documents]
from dbo.project root
inner join dbo.project sub
on root.projid = sub.projpid
left join dbo.project_content items
on items.projid = sub.projid
left join dbo.content docs
on items.item_id = docs.docnum
where root.projid = 370606.0
order by sub.projname asc

returns;


Root Folder	Sub Folder		Documents
Root Folder 1	Child Subfolder 1	Document
Root Folder 1	Child Subfolder 1	Document
Root Folder 1	Child Subfolder 2	Document
Root Folder 1	Child Subfolder 2	Document
Root Folder 1	Child Subfolder 3	Document
Root Folder 1	Child Subfolder 4	Document

Where I break down is trying to map this query to a recursive query, so that my output actually looks like this;


Root Folder	Sub Folder	Child Documents	Sub-Sub Folder		Grandchild Documents	Sub-Sub-Sub Folder	Great-GrandChild Documents
Root Folder 1	Child Subfolder	Document	NULL			NULL			NULL			NULL
Root Folder 1	Child Subfolder	Document	NULL			NULL			NULL			NULL
Root Folder 2	Child Subfolder	NULL		Grandchild Subfolder	Document		NULL			NULL
Root Folder 2	Child Subfolder	NULL		Grandchild Subfolder	Document		NULL			NULL
Root Folder 3	Child Subfolder	Document	Grandchild Subfolder	Document		NULL			NULL
Root Folder 4	Child Subfolder	NULL		Grandchild Subfolder	NULL			GrtGrandchild Subfolder	Document

With the appearance of NULL indicating no folder, or no folder content.

I'm almost ready to believe this just isn't possible

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources