Sql Query Give Title Isbn and Publisher of All Art Books Whose Authors Live in Us

SQL query for co-authored books

SQL expert Rudy Limeback gives advice for writing a query that involves a number of tables.

My book tabular array has iv instances of co-authored books, but I cannot remember them. And then in other words, I'thou trying to listing the book (id, title, and price) and writer (id, name) and publisher (id, name) info for all co-authored books in the collection. This is what I have and then far to join the tables together:

                SELECT book.id, book.championship, book.toll,        writer.id, author.name,        publisher.id, publisher.name FROM book, writer, publisher, writtenby WHERE book.id = writtenby.bookid AND writtenby.authorid = writer.id AND book.publid = publisher.id;              

I'1000 not sure where to go from here to go the iv rows.

Whenever a query involves a number of tables, I find it helpful always to start writing the join with the most restrictive table first.

For example, if you are writing a query to call up all the books from a specific publisher, the brake in this query would exist the specific publisher. Think of it as the difference between:

• Call back all books, then retrieve the publisher for each book, and then throw away both the book and the publisher if the publisher isn't the right one.

versus

• Call up the specific publisher, then retrieve only the books for that publisher.

Does that brand sense? See the divergence?

Okay, and so my rules for writing the FROM clause are:

1. First with the most restrictive table first.

2. If the commencement table has whatever many-to-ane relationships, bring together those tables next. Specifically, for each row of the starting time table, there is at most i row of the joined table(s).

3. If any of the tables thus far joined has whatever one-to-many relationships, you can join only one of these. Otherwise, if you bring together more than than 1 one-to-many table, yous get cross join effects.

4. Continue by joining many-to-one tables as necessary.

And then, in your query, the tabular array you want to start with is books. Why is this near restrictive? Because you want simply co-authored books, and there are many fewer co-authored books than those written by a unmarried writer.

Next, join to publishers, because a given book has only one publisher. (If a book has ii publishers, information technology isn't the aforementioned volume. It might take the same name, but it'll have a different ISBN.)

Now y'all tin can join to writtenby, which is your books-to-authors human relationship table. Specifically, for a given book, it is a one-to-many relationship.

Finally, you can join to authors, considering from writtenby to authors, information technology'southward a many-to-one relationship.

This should all make a lot more sense if you were to abandon the one-time style FROM clause comma-delimited list of tables with WHERE conditions, and started using JOIN syntax:

SELECT book.id      , book.title      , book.cost      , author.id      , author.name      , publisher.id      , publisher.proper noun   FROM book INNER   JOIN publisher     ON publisher.id = book.publid INNER   JOIN writtenby     ON writtenby.bookid = volume.id INNER   JOIN author     ON author.id = writtenby.authorid              

Doesn't that look improve? The intent is certainly clearer, I retrieve.

Now the simply remaining chore is to restrict the first table, the books tabular array, to just co-authored books. That requires a subquery:

                WHERE book.id IN        ( SELECT bookid            FROM writtenby          GROUP               BY bookid          HAVING COUNT(*) > 1 )              

The subquery is uncorrelated, which ways it can exist executed before the principal query, to produce a list of book ids for only co-authored books. Thus the main query is most efficient because it retrieves the publisher and writer data only for those books.

Dig Deeper on Oracle development languages

• 

  How to implement OpenID Connect for single-folio applications

  

  By: Kyle Johnson

• 

  DBMS keys: Primary, super, strange and candidate keys with examples

  

  By: Marker Whitehorn

• Database normalization in MySQL: Four quick and easy steps

• 

  Parallel execution: Determining SQL suitability, benefits

Related Q&A from Rudy Limeback

Using the SQL GROUP BY clause for counting combinations

Read SQL practiced Rudy Limeback's communication for counting combinations in a table with SQL'south GROUP BY clause  Continue Reading

How to sort an SQL UNION query with special Lodge BY sequence

SQL expert Rudy Limeback explains how to sort an SQL Marriage query using a special ORDERY BY sequence.  Continue Reading

Using an SQL SELECT statement from a not-existing table

SQL skilful Rudy Limeback explains how to codify a query using an SQL SELECT statement from a non-existing table.  Go along Reading

martinezthattery.blogspot.com

Source: https://www.techtarget.com/searchoracle/answer/SQL-query-for-co-authored-books

Share this post