help with taking 2 indexes from 1 field

[darthyoda6]

Hi, another newbie. I've been working on a book database, and I'm having problems with books that have multiple authors. My origional database didn't have an index, but I'm now finished making the indexes and converting the authors name in the book database to just the author index #'s. I know how to work when the book only has one author, but not when there's more then one author.

IE.
Database structure:

CREATE TABLE `books` (
  `ColIndex` smallint(3) unsigned NOT NULL,
  `Vol` tinyint(3) unsigned default NULL,
  `Series` tinytext,
  `Title` tinytext NOT NULL,
  `Author` tinytext NOT NULL,
  `PubYear` tinytext NOT NULL,
  `Publisher` tinytext NOT NULL,
  `ISBN` char(10) NOT NULL,
  `ColStatus` tinytext NOT NULL,
  `BookId` smallint(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Books I own';

Way entry is now:

310, 8, 'Sabrina, the Teenage Witch', 'Salem On Trial', 'Weiss, David Cody, Weiss, Bobbi J. G.', '1998', 'Simon Spotlight Entertainment', '0671017578', 'In Collection', 692

I want it to be:
310, 8, 'Sabrina, the Teenage Witch', 'Salem On Trial', '163, 144', '1998', 'Simon Spotlight Entertainment', '0671017578', 'In Collection', 692


When I use the query:

SELECT * FROM books, authors WHERE ColIndex = 310 AND books.Author = authors.AuthorId

I just get the first author from the list, the second one.

Any help would be appreciated, thanks.

[ronverdonk]

What is the structure of table 'authors'?

Ronald :cool:

[darthyoda6]

Sure, It's:

CREATE TABLE `authors` (
`AuthorId` int(11) NOT NULL auto_increment,
`AuthorName` tinytext NOT NULL,
`AuthorSort` tinytext NOT NULL,
`AuthorWeb` tinytext,
PRIMARY KEY (`AuthorId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 173 ;