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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]] > Inviato: venerd́ 5 luglio 2002 20.16 > A: Gigi Di Leo; '[EMAIL PROTECTED]' > 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 > <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > 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 > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > 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 --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php