CODEWITHSUNDEEP
sqlpostgresqlsubquery
Brittany
Brittany

Reputation: 119

SQL - Select query to return only a unique answer or nothing

I've created a view to show the author who writes all the books within the database with the word 'python' in the title. The issue I'm having is making it return nothing if there's more than one author. This is the working code for the view, I know I need to either implement a subquery using aggregate functions (count) or use EXISTS, but I'm not sure how to get it to work. 

CREATE VIEW sole_python_author(author_first_name, author_last_name)
AS SELECT first_name, last_name
FROM authors, books
WHERE authors.author_id = books.author_id AND
    title LIKE '%Python%'
GROUP BY authors.author_id;

The 'authors' table:

CREATE TABLE "authors" (
    "author_id" integer NOT NULL,
    "last_name" text,
    "first_name" text,
    Constraint "authors_pkey" Primary Key ("author_id")
);

The 'books' table:

CREATE TABLE "books" (
    "book_id" integer NOT NULL,
    "title" text NOT NULL,
    "author_id" integer REFERENCES "authors" (author_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
    "subject_id" integer REFERENCES "subjects" (subject_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE   ,
    Constraint "books_id_pkey" Primary Key ("book_id")  
);

If there is only one author who has written a book with 'python' in the title, it should return their name. If there is more than one, it should return nothing at all. Any help would be very much appreciated!

Upvotes: 0

Views: 655

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you want authors who have written books with Python in the name and no co-authors, then I think you need to aggregate at the book level, not the author level:

CREATE VIEW sole_python_author(author_first_name, author_last_name) AS
    SELECT DISTINCT MAX(first_name), MAX(last_name)
    FROM authors a JOIN
         books b
         ON a.author_id = b.author_id 
    WHERE a.title LIKE '%Python%'
    GROUP BY b.book_id
    HAVING COUNT(*) = 1;

This works because the MAX() is working on only one row.

The SELECT DISTINCT is because an author may have written more than one such book, but you only want the name once (presumably). Note: This does assume that you are looking for distinctness on the author name level and not the author_id level.

I find your question ambiguous. This answers the question: "What authors have been the sole authors of books with "Python" in the title?"

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

So only return a row if there's no other author?

I think this matches your description:

SELECT min(a.first_name), min(a.last_name)
FROM authors AS a JOIN books AS b
ON a.author_id = b.author_id 
WHERE b.title LIKE '%Python%'
HAVING COUNT (DISTINCT b.author_id) = 1;

Upvotes: 1

olesakn
olesakn

Reputation: 206

CREATE VIEW sole_python_author(author_first_name, author_last_name) 
AS 
SELECT first_name, last_name 
FROM authors, books 
WHERE authors.author_id = books.author_id 
AND title LIKE '%Python%'
GROUP BY first_name, last_name
HAVING COUNT(*) = 1

Upvotes: 0

Related Questions