Select Transitive Relation on One Table

Question

I have one table (in MySQL) like this:

subject | predicate | object
A       | P1        | X
X       | P2        | B

I want to know transitive relation by using this table, so the result should like this:

element1 | predicate1 | pivot | predicate2 | element2
A        | P1         | X     | P2         | B

I have been tried to construct the query by using nested query, but in the end i have syntax error (I think p2 (in nested query) cannot be determined in main query)

select p1.subject, p1.predicate, p1.object, p2.predicate, p2.object 
from some_relation p1 
where p1.subject = 'A' 
and p1.object = (select p2.subject from some_relation p2 where p2.object = 'B');

Anyone know how to do this kind of query? Is it possible?

score 1 · Answer 1 · answered May 06 '14 at 16:57

I would write a simple JOIN:

select p1.subject, p1.predicate, p1.object, p2.predicate, p2.object 
from some_relation p1 
inner join some_relation p2 on p1.object = p2.subject and p2.object = 'B'
where p1.subject = 'A'

Or do you mean something different?

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

Use a self join:

SELECT 
a.subject as element1, 
a.predicate as predicate2, 
a.object as pivot, 
b.predicate as predicate2, 
b.subject as element2 FROM
table a JOIN
table b ON a.object = b.subject;

edited May 23 '17 at 12:28

Community

1
1

answered May 06 '14 at 16:57

wils484

275
1
3
14

I choose your answer as accepted answer because of the reference link! Thank u for all the answer. – Wayan Wiprayoga May 06 '14 at 17:55

score 1 · Answer 3 · answered May 06 '14 at 16:58

You'd have to join the table with itself:

select p1.subject, p1.predicate, p1.object, p2.predicate, p2.object 
from some_relation p1 inner join some_relation p1  
  on p1.object = p2.subject
where p1.subject = 'A';

Please note that you cannot do this indefinitely. E.g., the above query only can return one "hop". If you have a transitive chain, that's hard to impossible to do in plain SQL.

Select Transitive Relation on One Table

3 Answers3