Mysql inner join from two or more variables?

Question

I have a table like: called BOOKS

+------+-------------------+---------------+
| id   |  book_title       |  author       |
+------+-------------------+---------------+
| 1    |  learning mysql   |  1234 12      |
+------+-------------------+---------------+
| 2    |  learning php     |  125 50       |
+------+-------------------+---------------+

And i want a VIEW from BOOKS and AUTHOR:

+------+-------------------+  
| id   |  author           |
+------+-------------------+
| 12   |  JOHN             |
+------+-------------------+
| 50   |  PAUL             |
+------+-------------------+
| 125  |  CHRISTOPHER      |
+------+-------------------+
| 1234 |  PATRICK          |
+------+-------------------+

So that i can have a VIEW which i should be something like the table which is below but should show instead of author id author name from tabel AUTHOR.

+------+-------------------+-----------------------------------+
| id   |  book_title       |  author                           |
+------+-------------------+-----------------------------------+
| 1    |  learning mysql   |  PATRICK                          |
+------+-------------------+-----------------------------------+
| 1    |  learning mysql   |  CHRISTOPHER                      |
+------+-------------------+-----------------------------------+
| 2    |  learning php     |  JOHN                             |
+------+-------------------+-----------------------------------+
| 2    |  learning php     |  PAUL                             |
+------+-------------------+-----------------------------------+

You should normalize your DB, that is, have a table that matches book_ids with author_ids. You will definitely run into problems with multiple values in a single column — knittl, Mar 04 '12 at 11:44

Vyktor · Accepted Answer · 2012-03-04T18:29:04.713

Your database design is wrong. books.author should be INT and contain foreign key to author.id. Table author should contain name VARCHAR(255) (or two columns referencing to author_name, I'm still not sure whether you have two authors or you split name into 2 entries).

So correct design would be:

BOOKS (
  id INT,
  book_title VARCHAR(255),
  author INT, -- only if each book has just one author
  PRIMARY KEY (id)
)

AUTHOR (
  id INT,
  name VARCHAR(255),
  first_name_id INT, -- If you want to split names into more columns
  PRIMARY KEY (id)
)

-- If you need more authors for one book
-- you maybe should keep original (primary) author id
BOOK_AUTHOR (
  book_id INT,
  author_id INT,
  PRIMARY KEY (book_id, author_id)
);

Than you may select data with:

SELECT BOOKS.id, BOOKS.book_title, AUTHOR.name AS author
FROM BOOKS
-- Study difference between left and inner joins
INNER JOIN AUTHOR on AUTHOR.id = BOOKS.author

And if you need to have more authors for one book:

SELECT BOOKS.id, BOOKS.book_title, AUTHOR.name AS author
FROM BOOKS
LEFT JOIN BOOK_AUTHOR on BOOK_AUTHOR.book_id = BOOKS.id
LEFT JOIN AUTHOR on AUTHOR.id = BOOK_AUTHOR.author_id

And you may need to output authors as Wolfgang Goethe; Oscar Wilde, than you can use GROUP_CONCAT:

SELECT BOOKS.id, BOOKS.book_title,
       GROUP_CONCAT( AUTHOR.name SEPARATOR '; ') AS author
FROM BOOKS
LEFT JOIN BOOK_AUTHOR on BOOK_AUTHOR.book_id = BOOKS.id
LEFT JOIN AUTHOR on AUTHOR.id = BOOK_AUTHOR.author_id
GROUP BY BOOKS.id

I have there two authors but the problem is there sometime more than 20 author it's kind a online article community. — Krueger, Mar 04 '12 at 12:03
@drake so I hope you understand the second part of the answer and table `BOOK_AUTHOR` if not, feel free to ask. — Vyktor, Mar 04 '12 at 12:48
Nice detailed response but the BOOK_AUTHOR primary key should be (book_id, author_id) instead of having the superfluous surrogate key. — user1191247, Mar 04 '12 at 18:16

score 0 · Answer 2 · edited May 23 '17 at 12:29

As knittl notes in the comments, you should definitely fix your database design. That said, here's a "brute force and ignorance" solution using your existing schema:

CREATE VIEW book_author (id, book_title, author)
AS SELECT book.id, book.book_title, author.author
FROM book
  JOIN author ON FIND_IN_SET(author.id, REPLACE(book.author, ' ', ',')) > 0

But this would be a very slow and ugly solution, even if it works. A much better solution is to get rid of the author column in your book table, and instead add a link table like this:

CREATE TABLE book_author (
  book INTEGER NOT NULL,
  author INTEGER NOT NULL,
  PRIMARY KEY (book, author),
  UNIQUE KEY (author, book)   /* for "all books by author X" queries */
)

(Unlike Vyktor, I don't feel that adding extra ID columns to simple link tables is a good idea. There's already a perfectly good natural primary key in that table — it doesn't need a surrogate key.)

Then you can create your view much more easily and efficiently:

CREATE VIEW book_author (id, book_title, author)
AS SELECT book.id, book.book_title, author.author
FROM book
  JOIN book_author ON book_author.book = book.id
  JOIN author ON book_author.author = author.id

Mysql inner join from two or more variables?

2 Answers2