Re: Query Problem

Wed, 16 Mar 2005 06:40:37 -0800

You may be able to get something like what you describe using GROUP_CONCAT(), if you have mysql 4.1, though it would be easier to get a comma separated list of authors than separate lines. See the manual for details <http://dev.mysql.com/doc/mysql/en/group-by-functions.html>.

In my opinion, however, you are confusing presentation of data with how it is accessed. Usually, presentation is the job of your code, not SQL. If I were doing this, I would

  SELECT book.title, author.name
  FROM book
  JOIN authorbook ON book.bookid = authorbook.bookid
  JOIN author ON authorbook.authorid = author.authorid
  ORDER BY book.title, author.name;

to get the data. (Notice I left out DISTINCT. There's something wrong with the data in your tables if DISTINCT is needed here.) To get the format you desire when printing the results, my code would only print the value of book.title if it is different from the value of book.title I previously printed. Something like (pseudo code):

  last_title = ''
  for each result_row
  {
    get title and author from result_row
    if title != last_title
    { # first row of a new book, so print the tile
      print title
      last_title = title
    }
    else
    { # another author for the same book
      print blank space
    }
    print author
  }

That's pretty easy to translate into real code in every language I know.

Michael


Xristos Karvouneas wrote: 
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: sol beach <[EMAIL PROTECTED]>
Reply-To: sol beach <[EMAIL PROTECTED]>
To: Xristos Karvouneas <[EMAIL PROTECTED]>
Subject: Re: Query Problem
Date: Tue, 15 Mar 2005 12:33:24 -0800

http://www.catb.org/~esr/faqs/smart-questions.html

It would help a lot to get answers if you shared the description of
all three tables.

On Tue, 15 Mar 2005 22:17:52 +0200, Xristos Karvouneas
<[EMAIL PROTECTED]> 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;
>
> 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/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>


_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to