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 2008 Forums
 Transact-SQL (2008)
 How to compare parent child data in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AhmetEmre90
Starting Member

Turkey
5 Posts

Posted - 02/20/2013 :  06:11:39  Show Profile  Reply with Quote
Hi;

I have two SQL tables like below;
T1: relationID, meterID, parentID, childID
T2: dataID, meterID, date, amount

Sample data of tables;
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  

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
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 02/20/2013 :  08:06:35  Show Profile  Reply with Quote
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

Turkey
5 Posts

Posted - 02/20/2013 :  08:22:35  Show Profile  Reply with Quote
Numbers at the picture are the meterID's.



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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/20/2013 :  08:34:19  Show Profile  Reply with Quote
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
  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