Validate each parent intermediary is also a parent

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)

Validate each parent intermediary is also a parent

Author

Topic

ramuu
Starting Member

7 Posts

Posted - 2013-05-14 : 07:45:37

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

52326 Posts

Posted - 2013-05-14 : 08:03:22

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

ramuu
Starting Member

7 Posts

Posted - 2013-05-14 : 08:22:51

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 00:38:27

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

ramuu
Starting Member

7 Posts

Posted - 2013-05-15 : 02:11:21

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 02:17:33

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

ramuu
Starting Member

7 Posts

Posted - 2013-05-15 : 02:52:21

yes.....I want to validate where each intermeiary parent's will be intermediary parent itself. I want to write query for this....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 03:46:06

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

ramuu
Starting Member

7 Posts

Posted - 2013-05-15 : 04:30:11

`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....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources