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
Advertisement
Answer
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