Peter Swift Peter Swift - 7 months ago 21
SQL Question

SQL join both ways to one result

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


Q1)
Two tables

Table: "TestItem"

ID, ITEM
1, "John Doe"
2, "Peggy Sue"
3, "Papa Sue"


Table: "Connector"

MOTHER, CHILD
1,2


The connector table will be used for several purposes (see below), but this is a destilled scenario for the equal type connection, like for instance marriage. If "John Doe" is married to "Peggy Sue" that information should also be sufficient to return "Peggy Sue" as married to "John Doe".
I can do this in two queries, but for efficiency (especially regarding my question 2) I'd appreciate this done in one query, so an implementation is not dependent on which way the connection is defined.
What is the most efficient way to do this?

Two queries approach to illustrate how the data can be fetched, but how one connection is missed one way or the other.

//Connector through "mother"-part SELECT ITEM, SUBITEM FROM TestItem
INNER JOIN (
SELECT MOTHER, ITEM AS SUBITEM
FROM Connector
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
FROM Connector
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
*/


Q2) Having the two approaches returned in one result may increase the amount of data involved, and hence bring down performance. If my focus is Peggy Sue, I assume sorting out only the relevant data as early as possible will improve performance. Is there a neat way of doing this from top level, or will every sub-query require an added WHERE?




PS: Some more information of the bigger perspective.
I'm planning to use the connector table for several purposes, both of the mentioned equal type, like colleagues, family, friends, etc, but also for hierarchical connection types like mother/child, leader/employee, country/city.
Thus solutions eliminating the mother/child-type connection may not suit my bigger purpose.
Basically I'm requesting how to handle the equal type of connections without losing the opportunity to use the same architecture and data for hierarchical connections.
Peggy Sue may through the same dataset be defined as daughter of Papa Sue through the relation

Mother, Child, Mother_type, Child_type
3, 2, Father, Daughter
1, 2, Married to, Married to


(But this is as mentioned on the side of what I'm requesting here. )

Answer

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'
Comments