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)
 Validate each parent intermediary is also a parent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ramuu
Starting Member

7 Posts

Posted - 05/14/2013 :  07:45:37  Show Profile  Reply with Quote

I have a table for which I want to validate where each parent intermediary is also a parent to itself. How to write query to validate this?

COLUMN DATATYPE DESCRIPTION
IntermediaryPK int
SourceSystemID tinyint Intermediary source
IntermediaryID nvarchar(20) Intermediary ID
IntermediaryName nvarchar(200) Intermediary name
IntermediaryTypeID nvarchar(1) Intermediary type
ParentIntermediaryID nvarchar(20) Intermediary parent
IntermediaryTypePK tinyint Intermediary type
IntermediaryCategoryPK smallint Intermediary category
ParentIntermediaryPK int Parent intermediary PK
GrandParentIntermediaryPK int Grand parent intermediary PK

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2013 :  08:03:22  Show Profile  Reply with Quote
can you show some sample data to illustrate how relationship exists and explain what you want to validate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ramuu
Starting Member

7 Posts

Posted - 05/14/2013 :  08:22:51  Show Profile  Reply with Quote
Hi visakh16
Here is the sample data
IntermediaryPK SourceSystemID IntermediaryID IntermediaryName IntermediaryTypeID IsActive LicenseID ParentIntermediaryID IntermediaryTypePK IntermediaryCategoryPK ParentIntermediaryPK GrandParentIntermediaryPK
6768 1 200509907481 IBL-BANK-KERALA-PALAKKAD C 1 NULL 201804759375 2 5 5440 NULL
6769 1 201837486436 ALLFIN - KERALA- PALLAKAD C 0 NULL 202072185454 2 5 5441 NULL
6770 1 200754186125 IBL-VFD-KERALA- NORTH-MALAPPURAM C 0 NULL 2018047593750001 2 5 4870 19758
6771 1 201521454945 ALLFIN - KERALA- TRISSUR C 0 NULL 202072185454 2 5 5441 NULL
6772 1 200917687453 ALLFIN -TAMILNADU-MADURAI- TUTICORIN C 0 NULL 202072185454 2 5 5441 NULL
6773 1 201211783821 IBL-IMFSL-TAMILNADU-TIRUNELVELI C 0 NULL 200891460243 2 5 6619 NULL



I just need to validate whether each parent intermediary is also a parent to itself. I think the query should be in format like
SELECT X
FROM Y
WHERE Z IS NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  00:38:27  Show Profile  Reply with Quote
Sorry I dont understand.
Please post details in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ramuu
Starting Member

7 Posts

Posted - 05/15/2013 :  02:11:21  Show Profile  Reply with Quote
Hi visakh16
Here is the sample data

intermediarypk intermediaryid parentintermediaryid parentintermediarypk grandparentintermediarypk
37 2003840093340001 NULL NULL NULL
38 200279839266 NULL NULL NULL
39 201995093279 NULL NULL NULL
40 200003615409 201951376189 4005 NULL
41 201336769172 201951376189 4005 NULL
42 201441882733 201282938485 6460 NULL
....
.....


I just need to validate whether each parent intermediary is also a parent to itself. I think the query should be in format like
SELECT X
FROM Y
WHERE Z IS NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  02:17:33  Show Profile  Reply with Quote
what do you mean by parent to itself? do you mean a self relationship like ID->ID?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ramuu
Starting Member

7 Posts

Posted - 05/15/2013 :  02:52:21  Show Profile  Reply with Quote
yes.....I want to validate where each intermeiary parent's will be intermediary parent itself. I want to write query for this....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  03:46:06  Show Profile  Reply with Quote
so for above sample data what would be the output? show atleast one case where you've this relationship existing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ramuu
Starting Member

7 Posts

Posted - 05/15/2013 :  04:30:11  Show Profile  Reply with Quote
`Hi visakh16,

Thats what I want to validate this. This query should not give any output. It should only validate each parent intermediary is also a parent to itself. foe ex. If I want to 'Validate each client has a valid gender' I hace written a query which is:

SELECT *
FROM CLIENT
WHERE ISNULL(ClientGenderName,'') NOT IN
(SELECT ISNULL(Gender,'') FROM PARTY_DETAILS)

So the query format will be like this for our task, and this query will not give any output, It just give column names....
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.34 seconds. Powered By: Snitz Forums 2000