May 27, 2013 at 12:53 am
I have a table for which I want to validate where each parent intermediary is also a parent to itself. It means I want to write validation Query which should return invalid parents I If any..
--===== Create the test table
CREATE TABLE Intermediary
(
IntermediaryPK INT ,
IntermediaryID NVARCHAR(20),
IntermediaryName NVARCHAR(200),
IntermediaryTypeID NVARCHAR(1),
ParentIntermediaryID NVARCHAR(20),
IntermediaryTypePK TINYINT,
ParentIntermediaryPK INT,
GrandParentIntermediaryPK INT,
GrandParentIntermediaryID NVARCHAR(20)
)
--== Insert the test data into the test table
INSERT INTO Intermediary
(IntermediaryPK, IntermediaryID, IntermediaryName, IntermediaryTypeID, ParentIntermediaryID, IntermediaryTypePK, ParentIntermediaryPK, GrandParentIntermediaryPK, GrandParentIntermediaryID)
SELECT '552','200244584261','A','1','201841943403','1','6459','6459','201841943403'UNION ALL
SELECT '553','200983879092','B','1','200707895681','1','6462','6459','200707895681'UNION ALL
SELECT '554','200925413387','C','1','200707895681','1','6462','6462','200707895681'UNION ALL
SELECT '555','200472620781','D','1','200707895681','1','6462','6462','200707895681'UNION ALL
SELECT '556','201902784325','E','1','200707895681','1','6462','6462','200707895681'UNION ALL
SELECT '557','201874832909','F','1','200707895681','1','566','6462','200707895681'UNION ALL
SELECT '558','201264024229','G','1','200707895681','1','566','6462','200707895681'UNION ALL
SELECT '559','201725870455','H','1','201062751762','1','566','6462','200707895681'
May 27, 2013 at 1:09 am
You can apply a Check Constraint on ParentColumn where ParentColumnId <> MainId.
May 27, 2013 at 1:15 am
Can you please give me the query based on the columns IntermediaryPK and ParentIntermediaryPK ? So it should return the output, If any invalid parent is there for each IntermediaryPK ..
Thanks
May 27, 2013 at 1:17 am
manoj.ramaiah (5/27/2013)
Can you please give me the query based on the columns IntermediaryPK and ParentIntermediaryPK ? So it should return the output, If any invalid parent is there for each IntermediaryPK ..Thanks
I didnt understand your requirement clearly, but what I mentioned in my previous post will eliminate the table from having ParentID same as ID (i.e. The Parent cannot be same as is ID), try below query to accomplish this.
ALTER TABLE TableName
ADD CONSTRAINT CK_TEST CHECK (ParentID <> MainId)
May 27, 2013 at 2:05 am
U CAN VALIDATE BY JOINING SAME TABLE FOR ALL REFERENCES.
EG. SELECT C.INTERMEDIARYNAME,C.INTERMEDIARYID, P.INTERMEDIARYNAME
FROM TABLE1 C LEFT JOIN TABLE1 P WHERE C.INTERMEDIARYPK = P.PARENTINTERMEDIARYID
WHERE C IS CHILD TABLE AND P IS PARENT TABLE
May 27, 2013 at 2:22 am
Hi Aadhar..
I want to write a SELECT query where validation should return invalid parents If table has....But your query will return all valid parents I think.....
May 27, 2013 at 2:23 am
Hi Bhaskar,
I want to write a SELECT query where validation should return invalid parents in SELECT If table has....
May 27, 2013 at 3:00 am
I am not sure if I understand your requirements correctly
But I hope this what is what you want
SELECT*
FROMYourTableName AS YTN -- Replace your actual table name here
WHEREParentIntermediaryPK IS NOT NULL
ANDNOT EXISTS
(
SELECT*
FROMYourTableName AS YTN_I -- Replace your actual table name here
WHEREYTN_I.IntermediaryPK = YTN_I.ParentIntermediaryPK
ANDYTN.ParentIntermediaryPK = YTN_I.IntermediaryPK
)
If this is not what you want, I would request you to provide the DDL of the tables involved along with some sample data and expected results explaining the problem
If you don't know how to do this, please go through the link in my signature.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 4:01 am
Hi Kingston,
Thanks for your quick reply.
Actually I want to write a SELECT query where validation should return invalid parents in SELECT If table has any. I think we have to find out those ParentIntermediaryPK who does not have any parents. And those ParentIntermediaryPK we have to compare with IntermediaryPK and should return where there both are not equal.. This is what I understood with our task. What You say? If you agree Can you provide the query for this.
May 27, 2013 at 5:25 am
manoj.ramaiah (5/27/2013)
Hi Kingston,Thanks for your quick reply.
Actually I want to write a SELECT query where validation should return invalid parents in SELECT If table has any. I think we have to find out those ParentIntermediaryPK who does not have any parents. And those ParentIntermediaryPK we have to compare with IntermediaryPK and should return where there both are not equal.. This is what I understood with our task. What You say? If you agree Can you provide the query for this.
I don't think you have understood the requirements correctly
Ask help from some colleague who has better idea on what the requirement is and avoid programming based on what you or I think
Once the requirements are clear, you can post back with the DDL and sample data as I mentioned earlier.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 10:07 pm
Hi,
Table is:
SELECT [IntermediaryPK]
,[SourceSystemID]
,[IntermediaryID]
,[IntermediaryName]
,[IntermediaryTypeID]
,[IsActive]
,[LicenseID]
,[ParentIntermediaryID]
,[IntermediaryTypePK]
,[IntermediaryCategoryPK]
,[ParentIntermediaryPK]
,[GrandParentIntermediaryPK]
,[GCCategoryID]
,[AIMSCategoryID]
,[CreateSessionID]
,[UpdateSessionID]
,[DataInputLoadCode]
,[GrandParentIntermediaryID]
FROM [Intermediaries]
And the sample data is attaches as an excel file.....Please help me out
May 28, 2013 at 12:15 am
What is the expected result based on your sample data?
Also it would be easier for people to help you if you put all this in a ready to use format as mentioned in the articled I referred you to.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 28, 2013 at 4:04 am
Good try creating the DDL and sample data, but there were a few mistakes when I tried to execute it in my SSMS.
I have rectified those and have copied it below.
--===== Create the test table
CREATE TABLE Intermediary
(
IntermediaryPK INT ,
IntermediaryID NVARCHAR(20),
IntermediaryName NVARCHAR(200),
IntermediaryTypeID NVARCHAR(1),
ParentIntermediaryID NVARCHAR(20),
IntermediaryTypePK TINYINT,
ParentIntermediaryPK INT,
GrandParentIntermediaryPK INT,
GrandParentIntermediaryID NVARCHAR(20)
)
--== Insert the test data into the test table
INSERT INTO Intermediary
(IntermediaryPK, IntermediaryID, IntermediaryName, IntermediaryTypeID, ParentIntermediaryID, IntermediaryTypePK, ParentIntermediaryPK, GrandParentIntermediaryPK, GrandParentIntermediaryID)
SELECT '552','200244584261','A','1','201841943403','1','6459','6459','201841943403'UNION ALL
SELECT '553','200983879092','B','1','200707895681','1','6462','6459','200707895681'UNION ALL
SELECT '554','200925413387','C','1','200707895681','1','6462','6462','200707895681'UNION ALL
SELECT '555','200472620781','D','1','200707895681','1','6462','6462','200707895681'UNION ALL
SELECT '556','201902784325','E','1','200707895681','1','6462','6462','200707895681'UNION ALL
SELECT '557','201874832909','F','1','200707895681','1','566','6462','200707895681'UNION ALL
SELECT '558','201264024229','G','1','200707895681','1','566','6462','200707895681'UNION ALL
SELECT '559','201725870455','H','1','201062751762','1','566','6462','200707895681'
Can you tell us based on your sample data what is the expected output and why?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 29, 2013 at 9:55 pm
I need to do is first of all I have to get all parents records. This is done by joining Intermediries to itself on ParentIntermediaryID = IntermediaryID (inner join) Now, from all the parents return those parents that don't have any rows with ParentIntermediaryID = IntermediaryID.
May 29, 2013 at 11:53 pm
Hi,
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply