modify question and have recursive column added

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

SQL Server 2005 Forums

Transact-SQL (2005)

modify question and have recursive column added

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-07-29 : 06:31:32

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

52326 Posts

Posted - 2013-07-29 : 06:59:19

[code]
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 <> '#'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-07-29 : 07:25:24

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 07:29:21

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-07-29 : 07:41:33

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 02:42:54

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-07-30 : 03:03:57

Sorry for that, but if a user do beling To a division I need To consider that, otherwise it wont be correct

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 03:40:54

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-08-01 : 03:36:12

Works fine, thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 04:35:02

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources