Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 modify question and have recursive column added
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

558 Posts

Posted - 07/29/2013 :  06:31:32  Show Profile  Reply with Quote
Hi

I have the following Query....


SELECT DISTINCT TOP (100) PERCENT dbo.tbl_Products.Text, dbo.tbl_Products.NodeId, dbo.tbl_Products.ParentNodeId
FROM            dbo.tbl_DivisionAssociation INNER JOIN
                         dbo.tbl_NodeDivisionAssosiation ON dbo.tbl_DivisionAssociation.DivisionID = dbo.tbl_NodeDivisionAssosiation.DivisionID INNER JOIN
                         dbo.tbl_Products ON dbo.tbl_NodeDivisionAssosiation.NodeID = dbo.tbl_Products.NodeId
WHERE        (dbo.tbl_DivisionAssociation.UserID = 1)



What this does is to show all the rows in tbl_Products based on weather user is associated with a DivisionID and that DivisionID have the NodeID in tbl_NodeDivisionAssociation. I would like to show the NodeID's that are not associated in the tbl_NodeDivisionAssociation table.

The tbl_Products table is built as a tree structure, what this means is that a NodeID can have ParentNodeID value, if so it means that the Product is placed in that folder. For example like this....

NodeID ParentNodeID Text NavigateUrl

1 Books #
2 1 Inferno page.html
3 Manuals #
4 3 Sony #
5 3 Panasonic #
6 4 Sony X Model page.html



If possible I only like to retrieve the rows that have a ParentNodeID and also something else than # in the NavigateUrl field. In the case above the rows that should be displayed are...

Inferno
Sony X model


visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/29/2013 :  06:59:19  Show Profile  Reply with Quote

SELECT DISTINCT TOP (100) PERCENT p.Text, p.NodeId, p.ParentNodeId
FROM            dbo.tbl_Products p
LEFT JOIN       dbo.tbl_NodeDivisionAssosiation nda
ON nda.NodeID = p.NodeId
LEFT JOIN       dbo.tbl_DivisionAssociation da
ON da.DivisionID = nda.DivisionID
AND da.UserID = 1
WHERE p.ParentNodeID IS NOT NULL
AND p.NavigateUrl <> '#'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 07/29/2013 :  07:25:24  Show Profile  Reply with Quote
Hi

This Query give me all rows from tbl_Products, I would like to receive only the ones that doesn't have a nodeid value in the tbl_NodeDivisionAssosiation table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/29/2013 :  07:29:21  Show Profile  Reply with Quote

SELECT TOP (100) PERCENT p.Text, p.NodeId, p.ParentNodeId
FROM            dbo.tbl_Products p
WHERE p.ParentNodeID IS NOT NULL
AND p.NavigateUrl <> '#'
AND NOT EXISTS(SELECT 1
FROM        dbo.tbl_NodeDivisionAssosiation nda
WHERE nda.NodeID = p.NodeId)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 07/29/2013 :  07:41:33  Show Profile  Reply with Quote
Almost there, there is a relationship between tbl_DivisionAssociation and dbo.tbl_NodeDivisionAssosiation on DivisionID. The User (UserID in tbl_DivisionAssociation) can be part of several DivisionID so there must also be a join based on that. Also, is it possible to add the "folder" name as I mentioned?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/30/2013 :  02:42:54  Show Profile  Reply with Quote
quote:
Originally posted by magmo

Almost there, there is a relationship between tbl_DivisionAssociation and dbo.tbl_NodeDivisionAssosiation on DivisionID. The User (UserID in tbl_DivisionAssociation) can be part of several DivisionID so there must also be a join based on that. Also, is it possible to add the "folder" name as I mentioned?


But your explanation just said
I would like to receive only the ones that doesn't have a nodeid value in the tbl_NodeDivisionAssosiation table

so do you really need to care about DivisionID here?

for getting foldername you need to add a self join


SELECT TOP (100) PERCENT p.Text, p.NodeId, p.ParentNodeId,p1.Name AS FolderName
FROM            dbo.tbl_Products p
INNER JOIN dbo.tbl_Products p1
ON p1.NodeID = p.ParentNodeID 
WHERE  p.NavigateUrl <> '#'
AND NOT EXISTS(SELECT 1
FROM        dbo.tbl_NodeDivisionAssosiation nda
WHERE nda.NodeID = p.NodeId)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 07/30/2013 :  03:03:57  Show Profile  Reply with Quote
Sorry for that, but if a user do beling To a division I need To consider that, otherwise it wont be correct
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/30/2013 :  03:40:54  Show Profile  Reply with Quote
That was not clear from your explanation at all!
anyways try

SELECT TOP (100) PERCENT p.Text, p.NodeId, p.ParentNodeId,p1.Name AS FolderName
FROM            dbo.tbl_Products p
INNER JOIN dbo.tbl_Products p1
ON p1.NodeID = p.ParentNodeID 
WHERE  p.NavigateUrl <> '#'
AND NOT EXISTS(SELECT 1
FROM        dbo.tbl_NodeDivisionAssosiation nda
INNER JOIN       dbo.tbl_DivisionAssociation da
ON da.DivisionID = nda.DivisionID
AND da.UserID = 1
WHERE nda.NodeID = p.NodeId)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 08/01/2013 :  03:36:12  Show Profile  Reply with Quote
Works fine, thank you very much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 08/01/2013 :  04:35:02  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000