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    |
Mayday
  • 4,680
  • 5
  • 24
  • 58
  • 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`? – Radu Gheorghiu Jan 30 '17 at 09: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. – Steve Smith Jan 30 '17 at 09:31
  • Add to your questions that `id2` has a related field on 'id1' of implies table. It is a self-referenced table. – McNets Jan 30 '17 at 09:31
  • This question has a solution here: http://stackoverflow.com/questions/2199942/getting-hierarchy-data-from-self-referencing-tables – McNets Jan 30 '17 at 09: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` – Steve Smith Jan 30 '17 at 09:33
  • @Steve Smith I can not know the levels of recursion. Database will grow and the linked objects also, depending on external users – Mayday Jan 30 '17 at 09:35
  • You'll need a recursive query (which is not available in mysql, IIRC) – joop Jan 30 '17 at 10:32

0 Answers0