469,902 Members | 1,950 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

help with taking 2 indexes from 1 field

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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `books` (
  2.   `ColIndex` smallint(3) unsigned NOT NULL,
  3.   `Vol` tinyint(3) unsigned default NULL,
  4.   `Series` tinytext,
  5.   `Title` tinytext NOT NULL,
  6.   `Author` tinytext NOT NULL,
  7.   `PubYear` tinytext NOT NULL,
  8.   `Publisher` tinytext NOT NULL,
  9.   `ISBN` char(10) NOT NULL,
  10.   `ColStatus` tinytext NOT NULL,
  11.   `BookId` smallint(5) NOT NULL
  12. ) 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 17 '06 #1
2 1249
ronverdonk
4,258 Expert 4TB
What is the structure of table 'authors'?

Ronald :cool:
Dec 18 '06 #2
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 ;
Dec 18 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by WindAndWaves | last post: by
4 posts views Thread by Darryn Ross | last post: by
8 posts views Thread by Xu, Wei | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.