A self join query

Question

Given this relational schema

• people (pid, name, gender, age, email, city, state)
• books (ISBN, title, edition, publisher, year, rating)
• write (pid, ISBN) pid is a foreign key and refers to people (pid), ISBN is a foreign key and refers to books (ISBN)

I need to write a SQL query to find the authors who either never published co-authored books or only co-authored with others from the same state, return their names.

I know I need to use a self join but not sure how to do that.

Thanks!

Answer 1

I think Tim's reasoning is correct, but the query is not. It requires a self-join on writes:

select p.*
from people p
where not exists (select 1
                  from writes w join
                       writes w2
                       on w.isbn = w2.isbn join
                       people p2
                       on w2.pid = w.pid
                  where w.pid = p.pid and
                        p.state <> p2.state
                 );

Answer 2

Your two logical criteria can actually be condensed into saying that you only want authors who have not coauthored books with other from different states. So, we can write a query for that:

SELECT p1.name
FROM people p1
WHERE NOT EXISTS (SELECT 1 FROM write w INNER JOIN people p2 ON p2.pid = w.pid
                  WHERE w.pid = p1.pid AND p2.state <> p1.state);