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 2008 Forums
 Transact-SQL (2008)
 How to compare parent child data in SQL

Author  Topic 

AhmetEmre90
Starting Member

5 Posts

Posted - 2013-02-20 : 06:11:39
Hi;

I have two SQL tables like below;
[CODE]T1: relationID, meterID, parentID, childID
T2: dataID, meterID, date, amount[/CODE]
Sample data of tables;
[CODE] T1 T2
----------------- -------------------------
1 | 1 | null | 2 * 1 | 1 | 01,01,2013 | 100 *
2 | 1 | null | 3 * 2 | 2 | 01,01,2013 | 60 *
3 | 2 | 1 | 4 3 | 3 | 01,01,2013 | 40 *
4 | 2 | 1 | 5 4 | 4 | 01,01,2013 | 35
5 | 3 | 1 | 6 5 | 5 | 01,01,2013 | 25
6 | 3 | 1 | 7 6 | 6 | 01,01,2013 | 15
7 | 4 | 2 | null 7 | 7 | 01,01,2013 | 25
8 | 5 | 2 | null
9 | 6 | 3 | null
10 | 7 | 3 | null
[/CODE]
I want to compare if the sum of the children's amount is equal to parent's amount.

For example; meter1 is parent of meter2 and meter3 (lines with *). I want to check if 100 = 60 + 40.
How can i do this with SQL query.

You can see the relation between the meters at the image below.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 08:06:35
This can be done using recursive CTE's. But, looking at your data, I was unable to relate the picture with the data in Table1. In the picture it looks like relation = 1 is the root node. Yet in T1, it looks like that is not the case. Which column in T1 identifies a row as belonging to a node in the picture? Or may be I am interpreting that table incorrectly?
Go to Top of Page

AhmetEmre90
Starting Member

5 Posts

Posted - 2013-02-20 : 08:22:35
Numbers at the picture are the meterID's.



http://ahmetemremermer.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 08:34:19
see example here

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

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

Go to Top of Page
   

- Advertisement -