472,102 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,102 software developers and data experts.

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.

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 1291
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`)
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 leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.