filter out and output "top node"

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)

filter out and output "top node"

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-02 : 01:57:13

I have 3 tables that looks like this..

tbl_Order
CartID NodeID Qty

tbl_OrderInfo
CartID Adress DateOrdered

tbl_Products
NodeID ParentNodeID Text

These tables have data like this..

tbl_Order
11-22 2 3
11-22 5 2
11-22 4 34
11-22 6 1
33-44 6 1
33-44 2 1
33-44 4 5

tbl_OrderInfo
11-22 'Rocky road' '2008-11-01'
33-44 'Sunny road' '2008-12-14'

tbl_Products
1 NULL 'top node 1'
2 1 'product 1'
3 NULL 'top node 2'
4 3 'product 2'
5 3 'product 3'
6 3 'product 4'

tbl_Order hold a list of items a user have ordered, tbl_OrderInfo holds information about the adress associated with this order, and tbl_products holds information about the ordered products. Is it possible to retrieve a summary of ordered items like this.... based on a date range?

TopNode NodeID Text Qty
'top node 1' 2 'product 1' 4
'top node 2' 4 'product 2' 39
'top node 2' 6 'product 4' 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 02:28:04

[code];WIth CTE
(
NodeID,
ParentNodeID,
ParentText,
Text
)
AS
(
SELECT NodeID,
ParentNodeID,
CAST(NULL AS varchar(1000)),
Text
FROM tbl_Products
WHERE ParentNodeID IS NULL
UNION ALL
SELECT p.NodeID, p.ParentNodeID,c.Text, p.Text
FROM tbl_Products p
INNER JOIN CTE c
ON c.NodeID=p.ParentNodeID
)

SELECT c.ParentText,c.NodeID,
c.Text,t.Total
FROM CTE c
INNER JOIN (SELECT NodeID,SUM(Qty) AS Total
FROM tbl_Order
GROUP BY NodeID) t
ON t.NodeID=c.NodeID

OPTION (MAXRECURSION 0)[/code]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-02 : 06:51:57

Hi

Thanks for the reply!

When I run this query I get this error "Types don't match between the anchor and the recursive part in column "ParentText" of the recursive query "CTE""

Do you know what might be wrong

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 07:07:15

Cast both as VARCHAR(MAX)

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-02 : 07:09:59

I now have this...

;WIth CTE
(
NodeID,
ParentNodeID,
ParentText,
Text
)
AS
(
SELECT NodeID,
ParentNodeID,
CAST(NULL AS varchar(MAX)),
Text
FROM tbl_Products
WHERE ParentNodeID IS NULL
UNION ALL
SELECT p.NodeID, p.ParentNodeID,c.Text, p.Text
FROM tbl_Products p
INNER JOIN CTE c
ON c.NodeID=p.ParentNodeID
)

SELECT c.ParentText,c.NodeID,
c.Text,t.Total
FROM CTE c
INNER JOIN (SELECT NodeID,SUM(Qty) AS Total
FROM tbl_Order
GROUP BY NodeID) t
ON t.NodeID=c.NodeID

OPTION (MAXRECURSION 0)

But I get the same error...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 07:25:33

whats the datatype of text column?

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-02 : 07:38:12

nvarchar(255)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 07:42:37

CAST(c.Text AS NVARCHAR(MAX))

and

CAST(NULL AS nvarchar(MAX))

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-02 : 07:59:44

Wow, thats pretty impressive. I did not think ths was possible... Thanks a million!

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-02 : 08:16:59

Hi again

I see that I need to involve 2 more table that I can filter on. The table is "tbl_Login" and looks like this..

tbl_Login
[UID] [Name] CustID

tbl_Customer
CustID CustName

the UID value from tbl_Login exist in tbl_Order.
So I need to be able to filter based on the CustID value in tbl_Customer, lets say CustID = 2

Can you show me how this is done?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 09:11:32

[code];WIth CTE
(
NodeID,
ParentNodeID,
ParentText,
Text
)
AS
(
SELECT NodeID,
ParentNodeID,
CAST(NULL AS nvarchar(MAX)),
Text
FROM tbl_Products
WHERE ParentNodeID IS NULL
UNION ALL
SELECT p.NodeID, p.ParentNodeID,c.Text, p.Text
FROM tbl_Products p
INNER JOIN CTE c
ON c.NodeID=p.ParentNodeID
)

SELECT c.ParentText,c.NodeID,
c.Text,t.Total
FROM CTE c
INNER JOIN (SELECT DISTINCT NodeID,UID,SUM(Qty) OVER(PARTITION BY NodeID) AS Total
FROM tbl_Order o
INNER JOIN tbl_Login l
ON l.[UID]=o.[UID]
WHERE l.CustID=your value here) t
ON t.NodeID=c.NodeID

OPTION (MAXRECURSION 0)[/code]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-03 : 00:00:07

Excellent, Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:02:25

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources