Okay, here is a possible solution, you will have to
try it to see if it is successful. It is based on the
fact that a table can be joined to itself.
select b.book_title,ba.publisher_code AS Author,
bb.publisher_code AS Publisher
from books b, basic_data ba, basic_data bb
where b.author_code = ba.description
and b.author_code = bb.description;
Regards,
Michael
--- Gigi Di Leo <gdileo@stripped> wrote:
> I apologize to everybody, I wasn't very clear
> submitting my problem - Italian -> English :-)
>
> I try to give you an example:
>
> table BOOKS
> +-------------+----------------+------------+
> | author_code | publisher_code | book_title |
> +-------------+----------------+------------+
> | 1 | 2 | Title 01 |
> | 3 | 4 | Title 02 |
> | 5 | 6 | Title 03 |
> | 7 | 8 | Title 04 |
> | 9 | 10 | Title 05 |
> | 11 | 12 | Title 06 |
> +-------------+----------------+------------+
>
> table BASIC_DATA (ordered by record_type): contains
> Authors and Publishers
> +------+-----------------------+-------------+
> | code | description | record_type |
> +------+-----------------------+-------------+
> | 1 | gigi | 1 |
> | 3 | andrea | 1 |
> | 5 | marcello | 1 |
> | 7 | antonio | 1 |
> | 9 | rosa | 1 |
> | 11 | angelo | 1 |
> | 2 | mc graw hill | 2 |
> | 4 | jsoft | 2 |
> | 6 | apogeo | 2 |
> | 8 | microsoft press | 2 |
> | 10 | mondadori | 2 |
> | 12 | acme publisher | 2 |
> +------+-----------------------+-------------+
>
> In BASIC_DATA records with record_type = 1 are about
> Authors, records with record_type = 2 are about
> Publishers.
>
> The query I am looking for should return:
>
>
+-----------------+----------------+----------------------+
> | book_title | author | publisher
> |
>
+-----------------+----------------+----------------------+
> | Title 01 | gigi | mc graw hill
> |
> | Title 02 | andrea | jsoft
> |
> | Title 03 | marcello | apogeo
> |
> | Title 04 | antonio | microsoft press
> |
> | Title 05 | rosa | mondadori
> |
> | Title 06 | angelo | acme publisher
> |
>
+-----------------+----------------+----------------------+
>
> I hope this helps you to better understand my
> problem.
> If I could touch the database I would split
> BASIC_DATA into two different tables AUTHORS and
> PUBLISHERS. But I cannot touch the DB structure.
>
> Gigi
>
> -----Messaggio originale-----
> Da: Jed Verity [mailto:jed@stripped]
> Inviato: venerd> A: Gigi Di Leo; 'mysql@stripped'
> Oggetto: Re: Query problem
>
>
> Hello, Gigi,
>
> By "decoded", do you mean that you want the words
> "Author" and "Publisher"
> to appear in your table instead of "1" and "2"? And
> you can't create or
> modify tables? Short of replacing the codes in the
> columns, it seems to me
> that you'd need to have a decode table. Something
> like:
>
> +------+-----------+
> | code | type |
> +------+-----------+
> | 1 | Author |
> +------+-----------+
> | 2 | Publisher |
> +------+-----------+
>
> Right? Without this, or without inserting the actual
> values, or without
> running conditionals in PHP or whatever environment
> you're accessing the
> data in, you might be stuck. Maybe someone else has
> an idea...
>
> HTH,
> Jed
>
> I liked it when Gigi Di Leo wrote this to me:
>
> > Hello list.
> >
> > I have to interface a database, which I cannot
> modify because it is maintained
> > somewher else, where there are two tables:
> >
> > BOOKS
> > author_code
> > publisher_code
> > book_title
> >
> > BASIC_DATA
> > code
> > description
> > record_type
> >
> > In the table BASIC_DATA records are classified on
> the flag value:
> > flag=1 -> record is about Authors
> > flag=2 -> record is about Publishers
> >
> > Could you please suggest me the best query syntax
> to get BOOKS' data with
> > Authors and Publishers decoded ?
> >
> > Thank you very much for your help.
> >
> > Gigi Di Leo
> >
> >
> >
>
---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list
> archive)
> >
> > To request this thread, e-mail
> <mysql-thread113881@stripped>
> > To unsubscribe, e-mail
> <mysql-unsubscribe-jed=veritys.com@stripped>
> > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list
> archive)
>
> To request this thread, e-mail
> <mysql-thread113885@stripped>
> To unsubscribe, e-mail
>
<mysql-unsubscribe-mivanyo=yahoo.com@stripped>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
>
__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com
