I have two tables "TestItem" and "Connector" where Connector is used for relating two items in "TestItem".
I have two questions in prioritized order. But first, feel free to suggest alternative approaches. I'm open for suggestion to completely rethink my approach to what I want to achieve here.
Question 1) How to get relations both ways returned in the same result
Question 2) How to filter the most efficient way for specific items
1, "John Doe"
2, "Peggy Sue"
3, "Papa Sue"
//Connector through "mother"-part SELECT ITEM, SUBITEM FROM TestItem
INNER JOIN (
SELECT MOTHER, ITEM AS SUBITEM
INNER JOIN TestItem ON Connector.CHILD = TestItem.ID
) AS SUB ON TestItem.ID = SUB.MOTHER
/* WHERE ITEM = "John Doe" return "Peggy Sue" => Correct
WHERE ITEM = "Peggy Sue" return nothing => Wrong
//Connector through "child"-part SELECT ITEM, SUBITEM FROM TestItem
INNER JOIN (
SELECT CHILD, ITEM AS SUBITEM
INNER JOIN TestItem ON Connector.MOTHER= TestItem.ID
) AS SUB ON TestItem.ID = SUB.CHILD
/* WHERE ITEM = "John Doe" return nothing => Wrong
WHERE ITEM = "Peggy Sue" return "John Doe" => Correct
Mother, Child, Mother_type, Child_type
3, 2, Father, Daughter
1, 2, Married to, Married to
UNION ALL might be what you are looking for:
select mother.id as connectedToId, mother.item as connectedToItem, 'Mother' as role from TestItem ti join Connector c on c.child = t.id join TestItem mother on c.mother = mother.id where ti.item = 'John Doe' union all select child.id as connectedToId, child.item as connectedToItem, 'Child' as role from TestItem ti join Connector c on c.mother = t.id join TestItem child on c.child = child.id where ti.item = 'John Doe'