Find authors who ONLY wrote history books

authors who published only one book
one hit wonder authors
who wrote famous books
who wrote what book
notable authors
most novels written by one author
prolific authors
which human authors wrote the most books in the bible

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

Famous authors who only wrote one book, Sewell died only five months after its publication, but she lived to see its initial success. In honor of her legacy, here are eight authors — Sewell  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:

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

Authors With Only One Novel (88 books), 88 books based on 237 votes: Wuthering Heights by Emily Brontë, The Picture of Fiction · Graphic Novels · Historical Fiction · History · Horror · Memoir · Music This list is for authors of books who have written only one novel (books of as the author wrote more than one book (see also message above). William Shakespeare (1564-1616) Nationality: English. Known for: Romeo and Juliet, Hamlet, Othello, King Lear. Shakespeare, known as the “Bard of Avon,” is one of the best known English writers in history. He is credited with writing nearly 40 plays, more than 150 sonnets, and several poems.

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
)

List of prolific writers, Some writers have had prolific careers with hundreds of their works being published. While some best-selling authors have written a small number of books that novelist in history, is estimated to have sold 4 billion books, having written 69 of the books is not detailed on the sales page and the books are printed only  Welcome to OrderofBooks.com - the most comprehensive website on the internet for listing books in order. We provide detailed listings of all authors and characters/series in both publication order and chronological order. We currently have 3495 authors listed on the site and 2723 characters/series with more added each day.

14 Writers Who Only Wrote One Novel, And if those writers happen to have passed on, well, you're just SOL,… into a good old chapter book, but, hey, we've all got to take what we can get. follows a cast of characters through landmark moments in U.S. history. Amazon's Most Sold charts rank books according to the number of copies sold and pre-ordered through Amazon.com, Audible.com, Amazon Books stores, and books read through digital subscription programs (once a customer has read a certain percentage – roughly the length of a free reading sample).

50 Of The Best Historical Fiction Authors Writing Today, The best historical fiction authors write unputdownable books that help Are the author's historical novels award-winning and/or critically Her notable works include The Other Einstein, Carnegie's Maid, and The Only Woman in the Room. Lisa See is an American author whose historical novels include  4,038 books — 4,897 voters Best First Book by New Author. 1,368 books — 3,706 voters

The great writers forgotten by history - BBC Culture, They're just some of the overlooked authors we need to rediscover, writes Hephzibah Anderson. OK; Find out what's changed The creator of Billy Bunter, Charles Hamilton, reputedly wrote 100 million words (Credit: Alamy) Dennis was also the first writer in history to have three books on the New York  Read-Alikes at BookBrowse. Here you can find handpicked read-alike recommendations for more than 4000 contemporary books and 3000 authors. Use the alphabetical lists below to look up a favorite book or author and then click "See Read-Alikes" to discover similar books.

Comments
  • Thank you for your help.
  • Thank you. This explanation helps get me started. I have a lot to learn.
  • I like this one, however I do not understand the 'select 1' - maybe I'm not far enough in my course to understand this.