I have one table with 4 columns
ID, SourceID, TargetID, TargerType
1, 123, 456, 4
2 456, 123, 3
Id is unique key.
SourceID is UserID
TargetID is User Manager ID
TargetType is Manager Role
Please check above example in ID 1 user 123 report to 456
so I want to check is there any row exists where manager 456 report to 123. I need to ID for all these type of rows.
Thanks
>> I have one table with 4 columns <<
This is not a table; it has no key. This non-table has no name because you could not bother to post DDL. Does your boss make you program with specs like this. What your vague narrative seems to describe is called an adjacency list model for an organizational
chart.
>> Id is unique key. <,
Would explain what a NON-unique key would be? In RDBMS, there is no such absurdity as a generic, magic id; thanks to a principle of logic called The Law of Identity (which has nothing to do with IDENTITY table properties). This has to be a <something
in particular>_id ; my guess is that you need an emp_id to model an organizational chart.
CREATE TABLE Organization
(emp_id INTEGER NOT NULL PRIMARY KEY,
boss_emp_id INTEGER - null is root of tree
REFERENCES Organization(emp_id),
CHECK (emp_id <> boss_emp_id),
boss_job_type VARCHAR(12) NOT NULL);
>> so I want to check is there any row exists where manager 456 report to 123. I need to ID for all these type of rows. <<
I think you meant to check that there are no cycles so that this will be a tree model with (ugh!) fake pointer chains. Sorry, but you are stuck with cursors (either actual or hidden in recursive CTEs) that do traversals. Notice that all we can do is that one
CHECK() to keep the shortest cycle out of the tree.
Google the Nested Sets model and use it instead. I have a whole book on this topic; TREES & HIERARCHIES IN SQL.
How do you determine which one of the combinations that is the wrong one? Is that by TargetType?
You can try to see if the below will give you what you need -
SELECT * FROM YourTable T JOIN YourTable T1 ON T.SourceID = T1.TargetID AND T.TargetID = T1.SourceID AND TargetType = xYou'll then have to add the specific TargetType you want to look for (if you need that).
Above mention solution give only one or two level circular loop if we need third level circular loop then that query not give proper result Like:-
1=3
2=1
3=2
with C as
(
select T.Events,T.EventsParent,cast(','+cast(Events as varchar(36))+',' as varchar(max)) as Path,0 as Cycle from [Events] as T
-- where T.Events in(select EVENTS from eventMatters where matters = 'b7740699-b6ab-4f59-a480-ac0b3efa1bf8')
union all
select T.[Events],T.[EventsParent], C.Path + cast(T.Events as varchar(36))+',', case when C.Path like '%,'+cast(T.Events as varchar(36)) +
',%' then 1 else 0 end from Events as T inner join C on T.EventsParent =C.events where C.Cycle=0 and C.Events in(select EVENTS from eventMatters where matters = 'b7740699-b6ab-4f59-a480-ac0b3efa1bf8')
) select * from C where Cycle= 1;