A self join query

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

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
                 );