2

I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the tables:

Authors (Au_Id  PK, Au_fname, Au_lname, ...)

Titles (Title_Id PK, Title, Title_type, ...)

TitleAuthor (Au_Id PK, Title_Id PK, Au_order, ...)

In TitleAuthor, I am not sure of what Au_order means, but I suspect it is the order in which an author's name is cited in a book with more than one author. Au_order is a TinyInt data type, btw. These queries are supposed to be introductory, i.e. no fancy joins or subqueries.

This is what I have tried:

1)

Select Au_Id from `TitleAuthor` where  Count(Auth_id)>1;

But this gives us the author name, not the book name (remember: intro query)

2) Figuring out which books/titles are mentioned more than once in TitleAuthor:

Select Title_id from Title-Author where Count(Title_Id)>1;

This gives me no results for some reason, though no error messages either. I suspect I may need grouping and having, but this is not working either.

Any Ideas?

2
  • Sorry, what was your question? Commented Jan 16, 2016 at 10:05
  • Hi @Daniel Hutmacher :Daniel, just wanted a list of the books with more than one author.
    – MSIS
    Commented Jan 16, 2016 at 18:12

2 Answers 2

2
select Titles.Title_Id, Titles.Title
  from Titles 
  join TitleAuthor 
        on TitleAuthor.Title_Id =  Titles.Title_Id 
 group by Titles.Title_Id, Titles.Title 
having count(*) > 1
10
  • Please note I was looking for a basic query: no joins or subqueries.
    – MSIS
    Commented Jan 17, 2016 at 15:43
  • Please note the stated question is find "all books (Titles) with more than one author". Since that information is in two tables you cannot do it without joins or subqueries
    – paparazzo
    Commented Jan 17, 2016 at 15:52
  • But look at above reply: it was done within a single table.
    – MSIS
    Commented Jan 17, 2016 at 15:57
  • No it as in the stated question is not done with a single table. That query above does not return Title. You even capitalized, bolded and italicized Titles.
    – paparazzo
    Commented Jan 17, 2016 at 16:00
  • 1
    @MSIS So count is count. Title_id is not title. You literally asked to find "books (Titles)".
    – paparazzo
    Commented Jan 17, 2016 at 20:40
4

There are many ways. Probably simplest:

SELECT Title_id
FROM   TitleAuthor
GROUP  BY Title_id
HAVING count(*) > 1;

You can JOIN to Titles to get book details.

1
  • Thanks, re the last comment, please note this is intended to be a basic query,i.e., without joins nor subqueries.
    – MSIS
    Commented Jan 17, 2016 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.