The code for generating the tables is shown below:
create table book ( bookid char(12) not null, dimensions char(15), availability char(30), booktype char(20), publisher char(20), isbn char(20), itemsinstock int(3), price float(5,2), title char(50), toc char(24), picture char(36), primary key (bookid));
create table author ( authorid char(12) not null, name char(24), primary key (authorid));
create table authorbook ( bookid char(12) not null references book, authorid char(12) not null references author, primary key(bookid,authorid));
Basically, I want to get it in the format specified in the message, i.e. if the title is the same, I do not want it to be printed again...
Any ideas?
From: gerald_clark <[EMAIL PROTECTED]> To: Xristos Karvouneas <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: Query Problem Date: Tue, 15 Mar 2005 14:49:45 -0600
Xristos Karvouneas wrote:
Dear All,
I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like:
Title 1 Author 1 Author 2
Title 2 Author 3 Author 4
I have written the following:
select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid;
You want to add: ORDER BY title,name;
You probably are getting them all, but not in the order you expect.
hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword).
Is there any way I can modify the query so that it does what I want it to do?
I look forward to hearing from you soon.
Thanks in advance.
George
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]