Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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
52326 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  
 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.13 seconds. Powered By: Snitz Forums 2000