0

I have a table in my database called book_collection, which has columns: publisherID, title, familyID, bookID and another table called book_authors which has columns: bookID and authorID.

I want to join the tables together and then find all the "books" that have same publisherIDs and titles.

So I initially started with:

SELECT book_collection.publisherId, book_collection.title,
book_authors.authorId FROM book_collection INNER JOIN book_authors ON book_collection.bookId = book_authors.bookId

This would join the tables, but the problem I am having now is how can I perform any other queries to this table. If I copy this code and paste it after every SELECT * FROM ..., it gives me an error saying "every derived table needs an alias"?

I am not the best with SQL, so please go light with me. Any help will be greatly appreciated!

CSishard
  • 21
  • 5
  • 1
    `select * from (...your query here...) AS alias`, is all you need, exactly as the error message said. – Marc B Jan 13 '16 at 18:16
  • Possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) – Evgeny Jan 13 '16 at 18:16

3 Answers3

1

Every derived table needs an alias

This error means you need to use table alias in your columns with SELECT STATEMENT.

How can you use alias:

You can use table name as alias if you don't want to use custom alias.

Example: 

SELECT collection.* ,authors.* ,book_authors.* FROM ....

Other solution:

You can also use your custom define alias for example: 

SELECT c.* ,a.* , b.*     FROM collection c    INNER JOIN authors a ON a.columnid = c.columnid ....

Basic reason for this error:

The reason of this error is becuase you have same columns name in different table and MYSQL didn't know which column is the reference of which table. Using alias in these kind of query always helpful.

Side note:

I suggest you to use custom alias that easy to write multiple times. Basically aliases are created to make column names more readable.

For more basic information SQL ALIASES

devpro
  • 16,184
  • 3
  • 27
  • 38
0

you need an as clause.

check out this post here:

every derived table must have its own alias

it covers the same ground.

Community
  • 1
  • 1
pRose_la
  • 194
  • 1
  • 12
0
SELECT collection.* ,authors.*
  ,book_authors.authorId
  FROM book_collection as collection
  INNER JOIN book_authors as authors
  ON book_collection.bookId = book_authors.bookId

With this query you can get all the columns from the two tables. And if you want to make a better performance you can have more filters in the inner join or in the where clause

devpro
  • 16,184
  • 3
  • 27
  • 38
Betin Beton
  • 11
  • 2