2

I on looking for a sql code (preferably with joins, not with cursors or stored proc or variables).

We have a parent manager-employee relationship in a database table, where the manager erroneously becomes the employee for some of the succeeding verticals/records, Example below:

Pic of sample data

Reporting Manager   Employee
1                     2
2                     3
3                     4
4                     1   --<< Circular Reference

(The last record is wrong, and creates a circular reference with the first record, as 1 cannot report back to 4)

I am looking forward to find these records who pose this kind of a circular reference, any help is really appreciated.

I have attached a pic of how the data looks like.

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Souvik
  • 29
  • 5
  • 1
    Tag your DBMS please. – Serg Mar 31 '17 at 15:53
  • MySQL should be fine. – Souvik Mar 31 '17 at 15:56
  • See http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query just add a check for repeating path part. – Serg Mar 31 '17 at 16:17
  • I reckon youre talking about this one ? SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name = 'ELECTRONICS'; – Souvik Mar 31 '17 at 17:04
  • No, I mean recursion. The main problem here is that there can be a number of cycles. I don't believe they all can be detected with a single query, although listing all participating pairs in all cycles is rather simple. – Serg Mar 31 '17 at 18:41

1 Answers1

0

You should be able to achieve this by joining the table to itself. See below:

select b.Parent, b.Child
  from your_table a
  join your_table b
    on b.Child = a.Parent
 where b.Parent > a.Parent;
JuveLeo1906
  • 161
  • 5
  • Thx Juveleo1906, self join makes sense, but the last where clause may not work if the field has both text and numbers, like IP001. – Souvik Mar 31 '17 at 16:48
  • That's correct, unless the letters in the fields are just prefixes. If that were the case you could just `CAST` or `CONVERT` the field and it would still evaluate correctly. This wouldn't work if the letters are actually a meaningful part of the data. – JuveLeo1906 Mar 31 '17 at 18:24