Pfthb

New to SQL, although catching on - stuck on this query.
This works, although Paddy O'Furniture should not be showing up as this author hasn't written any book and his au_id does not appear in the title_authors table. Please help.

Find authors who ONLY wrote history books

select a.au_id, a.au_lname, a.au_fname

from authors a

where not exists(

 select ta.au_id

 from title_authors ta

 join titles t

 on t.title_id = ta.title_id

 where t.type != 'history'

 and a.au_id = ta.au_id

)

Output:

A01 Buchman Sarah

A07 O'Furniture Paddy

While your approach can work if you use another exists to make sure the author wrote at least a history book, here's another approach using conditional aggregation:

select a.au_id, a.au_lname, a.au_fname
from authors a
 join title_authors ta on a.au_id = ta.au_id
 join titles t on ta.title_id = t.title_id
group by a.au_id, a.au_lname, a.au_fname
having sum(case when t.type != 'history' then 1 else 0 end) = 0

Online Demo

Paddy O'Furniture is in your result because no matching rows were discovered in the correlated subquery. i.e. no rows exist for that author and hence where not exists is true.

select a.au_id, a.au_lname, a.au_fname
from authors a
inner join title_authors ta ON a.au_id = ta.au_id
inner join titles t on ta.title_id = t.title_id
group by a.au_id, a.au_lname, a.au_fname
having count(case when t.type <> 'history' then 1 end) = 0

The approach above uses a case expression within a count() function so that if any books have a non history type this count will be greater than zero. The having clause enables using aggregated values to filter the final result (having is used is AFTER the group by clause, and is not a substitute for a where clause).

You are close. Adding JOIN to title_authors on the outer query will filter out authors who have not written a book.

select a.au_id, a.au_lname, a.au_fname
from authors a
join title_authors ta1 on ta1.au_id = a.au_id
where not exists(
 select 1
 from title_authors ta
 join titles t on t.title_id = ta.title_id
 where t.type != 'history' and ta1.id = ta.id
)

The title_authors in the inner query can actually be removed.

select a.au_id, a.au_lname, a.au_fname
from authors a
join title_authors ta on ta.au_id = a.au_id
where not exists(
 select 1
 from titles
 where t.type != 'history' and title_id = ta.title_id
)