I have a table like this:
CREATE TABLE Foo (
id1 integer,
id2 integer
);
Each row is unique and is used to store the relationship between two entities. So, for instance, given the data:
INSERT INTO Foo VALUES (1, 2);
INSERT INTO Foo VALUES (1, 3);
INSERT INTO Foo VALUES (4, 1);
INSERT INTO Foo VALUES (2, 5);
INSERT INTO Foo VALUES (1, 2);
INSERT INTO Foo VALUES (6, 1);
INSERT INTO Foo VALUES (4, 7);
I can use the following query to find out all of the relationships that one id has:
SELECT id1 FROM Foo WHERE id2 = 1
UNION
SELECT id2 FROM Foo WHERE id1 = 1;
The output would be:
2
3
4
6
Now, let's say I wanted to find all relationships for all of the relationships for 1 so that the query returned all ids that where uniquely related to each relation of the specified id. The output should be:
5
7
Is there a single query that will get me those results or do I need to write a recursive stored procedure to find each of the relations for each subrelations?
Start Free Trial
