How do I link tables with id from a list stored as XML in a column

Question

Hopefully the question explains it well. I have a DB for a Library. Since they can be used many times, and contains more data than just a name, I have a table for Authors. Then there's a table for Books. I have no problem linking Authors to Books via a column called Author_id.

What I'm trying to do is have a column called Author_IDs that contains a list of id's, since a book can have multiple IDs. In the Author_IDs column I have:

<id>3478</id>
<id>6456</id>

Using the ExtractValue function in MySQL I can link the table with one or the other id using: WHERE Author.id = ExtractValue(Book.Author_IDs,"/id[2]") // to get the second ID.

My question is, I want to be able to automatically display all of the authors of a book, but don't know how to link to it more than once, without looping. How can I get the results to show me all of the authors?

(Or is there a better way to accomplish this?)

Answer 1

Firstly, I have to vote against your storage method. Storing data as xml inside a mysql column should be avoided if possible. If you use a normal approach you will find this problem to be much easier.

Create a table:

book_authors

book_id     author_id
-------     ---------
      1             1
      1             2
      1             3
      2             2
      2             4

Then to get all of the authors associated with a certain book it's a simple query.

Select
   b.book_id,
   b.book_name,
   GROUP_CONCAT(a.author_name) AS 'authors'
FROM
   book_authors ba LEFT JOIN
   books b ON ba.book_id = b.book_id LEFT JOIN
   authors a ON ba.author_id = a.author_id
GROUP BY
   ba.book_id

Answer 2

Not sure I understand completely. Could something like this do the trick?

select a.* from tblBooks b left join tblAuthors a on (b.authors = concat('%', a.id, '%') where b = 'book id';

Answer 3

I would have done it like this

Structure

tblBooks
--------
book_id
book_name

tblAuthors
----------
author_id
author_name

tblBooksToAuthors
-----------------
id
book_id
author_id

Query

select a.*
from tblAuthors a
left join tblBooksToAuthors b2a on (b2a.author_id = a.author_id)
where b2a.book_id = {your book id};