Transitive Property in SQL

Question

This is a question that I feel I should know, but I've been having a hell of a time being as expressive as I would like to be in SQL.

My question is a simple one: what is an idiom for expressing transitive relationships in SQL? A specific example:

Say I have the following schema:

user(email, name)
friends(friend1_email, friend2_email)

I'm having a problem expressing the following query:

Find users A, B, and C such that A is friends with B, B is friends with C, but C is not friends with A.

I will admit that this is homework, but I've been having conceptual issues with expressing queries. Any assistance would be appreciated. Thanks.

Answer 1

My advice with complex queries is always to start simple:

# 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, let's 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 a single query

# 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 friends with users B who are friends with users C, but does not limit the result set to those records where A and C are not friends. To get that 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