Re: [NEWBIE] Help with a simple query
From: Chris Leonard <s_p_a_m_chris_at_hotmail.com>
Date: Fri, 30 Jul 2004 12:55:20 -0500
Message-ID: <cYvOc.208$Qc.3836_at_news.uswest.net>
Date: Fri, 30 Jul 2004 12:55:20 -0500
Message-ID: <cYvOc.208$Qc.3836_at_news.uswest.net>
You get multiple lines for multi-author books because there are multiple row combinations from the table that satisfy your query. In other words, you are basically asking the question "which author wrote each book?" and for those books there is more than one answer. You will need to write a function (or some such thing) to retrieve the names of the authors into a comma-delimited string (or whatever you want), and then use the function instead of the a_name field. You may also have to use DISTINCT to get rid of duplicates. Alternatively, you could change your query so that it just displays the first author match for each book, but I doubt that would satisfy your requirements.
-- Cheers, Chris ___________________________________ Chris Leonard, The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com MCSE, MCDBA, OCP, CIW ___________________________________ "castor." <castorrr-removeME-_at_fastwebnet.it> wrote in message news:GoYNc.102722$G%.42816_at_tornado.fastwebnet.it...Received on Fri Jul 30 2004 - 19:55:20 CEST
> hi all,
>
> i have two tables
>
> BOOK
> ------------------------------------
> CODE NOT NULL NUMBER
> TITLE VARCHAR2(45)
> YEAR NUMBER
>
>
> AUTHOR
> ------------------------------------
> A_NAME NOT NULL VARCHAR2(15)
> DESCRIPTION VARCHAR2(40)
>
> i had an n->n relation with title/a_name, so i created a mid entity:
>
> BOOK_AUTHOR
> ------------
> TITLE
> A_NAME
>
> After that i created the following view
>
> create view ALL as
> (select Code, BOOK.Title, AUTHOR.A_Name
> from BOOK, BOOK_AUTHOR, AUTHOR
> where (BOOK.Title=BOOK_AUTHOR.Title AND
> BOOK_AUTHOR.A_Name=AUTHOR.A_Name);
>
> So, where's my problem? When a book is written by multiple authors. when
> i select the view, multiple authors are put in multiple lines. Is there
> any way out?
>
> thx for help, cas
>