Transitive property in SQL
This is a question I think I should be aware of, but I've had a time so expressive that I would like to be in SQL.
My question is simple: what is the idiom for expressing transitive relations in SQL? Specific example:
Let's say I have the following schema:
user(email, name) friends(friend1_email, friend2_email)
I am having a problem expressing the following query:
Find users A, B and C so that A is friends with B, B is friends with C, but C is not friendly with A.
I admit this is homework, but I am having conceptual problems expressing requests. Any help would be appreciated. Thank.
source to share
My advice with complex queries always starts out simply:
# Find friends A and B
select A.email as A_email, A.name as A_name, B.email as B_email, B.name as B_name
from user A
join friends
on A.email = friends.friend1_email
join user B
on B.email = friends.friend2.email
Simple enough, do it again for B and C:
# Find friends B and C
select B.email as B_email, B.name as B_name, C.email as C_email, C.name as C_name
from user B
join friends
on B.email = friends.friend1_email
join user C
on C.email = friends.friend2.email
Now let's combine to get A, B and C in one request
# Find friends A, B, and C
select A.email as A_email, A.name as A_name, B.email as B_email, B.name as B_name, C.email as C_email, C.name as C_name
from user A
join friends f1
on A.email = f1.friend1_email
join user B
on f1.friend2_email = B.email
join friends f2
on B.email = f2.friend1_email
join user C
on f2.friend2_email = C.email
The above query will give us all users A who are familiar with users B who are familiar with users C, but do not restrict the result set to reports where A and C are not friends. To get this result set, we'll have to modify our query a bit.
# Find friends A, B, and C
select A.email as A_email, A.name as A_name, B.email as B_email, B.name as B_name, C.email as C_email, C.name as C_name
from user A
join friends f1
on A.email = f1.friend1_email
join user B
on f1.friend2_email = B.email
join friends f2
on B.email = f2.friend1_email
join user C
on f2.friend2_email = C.email
left join friends f3
on A.email = f3.friend1_email
and C.email = f3.friend2_email
where
f3.friend1_email is null
source to share
Something like:
select *
from friends a
--If this joins, a and b are friends
left join friends b
on (a.friend1_email = b.friend1_email or
a.friend1_email = b.friend2_email or
a.friend2_email = b.friend1_email or
a.friend2_email = b.friend2_email)
--If this joins, b and c are friends
left join friends c
on (b.friend1_email = c.friend1_email or
b.friend1_email = c.friend2_email or
b.friend2_email = c.friend1_email or
b.friend2_email = c.friend2_email)
--If this joins, c and a are friends.
--Also making sure that a is same person as a2
left join friends a2
on (c.friend1_email = a2.friend1_email or
c.friend1_email = a2.friend2_email or
c.friend2_email = a2.friend1_email or
c.friend2_email = a2.friend2_email)
and (a.friend1_email = a2.friend1_email or
a.friend1_email = a2.friend2_email or
a.friend2_email = a2.friend1_email or
a.friend2_email = a2.friend2_email)
where b.friend1_email is not null --join was made, a and b are friends
and c.friend1_email is not null --join was made, b and c are friends
and a2.friend1_email is null --join was NOT made, a and c are NOT friends
source to share