0

Currently, I am using MySQL

I have 2 tables:

Table objects:

| id | desc |
| 1  | 'A'  |
| 2  | 'B'  |
| 3  | 'C'  |
| 4  | 'D'  |
| 5  | 'E'  |

Table implies:

| id1 | id2 |
| 1   | 2   |
| 1   | 3   |
| 2   | 4   |
| 2   | 5   |
| 3   | 5   |

Table Implies columns id1 and id2 are both foreign keys to table objects id.

Which means:

object 1 implies object 2 and object 3.

object 2 implies object 4 and object 5

object 3 implies object 5

Therefore, Object 1, implies 2, 3, and 4 and 5 (Since 2 implies 4 and 5)

Is there any way to create a query for this behaviour? My current solution consists on calling recursively from java the query until I don't get any new info:

SQL Query Im calling:

SELECT o.id, i.id2 
FROM objects o 
    INNER JOIN implies i ON o.id = i.id1 WHERE id = 1;

I first call it with id = 1, then with id IN (2, 3)... etc

The kind of result I want to achieve:

SELECT ... WHERE id = 1

should return:

| id | id2  |
| 1  | 2    |
| 1  | 3    |
| 1  | 4    |
| 1  | 5    |
7
  • It looks like your JOIN by itself (remove the WHERE clause) would do the exact same thing as doing "a loop" going over all ID values. Are you maybe looking for a result set for each ID? Commented Jan 30, 2017 at 9:30
  • Because 1 implies ("is linked to") 2 and 3, and 2 is linked to 4 and 5. (3 is also linked to 5). Only the second table is important. Commented Jan 30, 2017 at 9:31
  • Add to your questions that id2 has a related field on 'id1' of implies table. It is a self-referenced table. Commented Jan 30, 2017 at 9:31
  • This question has a solution here: stackoverflow.com/questions/2199942/… Commented Jan 30, 2017 at 9:32
  • @Mayday, if you know the maximum number of levels of recursion, you could just INNER JOIN more implies tables, e.g. ...INNER JOIN implies2 i2 ON i.id = i2.id1 Commented Jan 30, 2017 at 9:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.