SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

524 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
52323 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

524 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
52323 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

524 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
52323 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

524 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
52323 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

524 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
52323 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000