Is it possible to do two temporary tables for authors and publishers and then do a join on the title?
-----Original Message----- From: Jed Verity [mailto:[EMAIL PROTECTED]] Sent: 05 July 2002 21:01 To: '[EMAIL PROTECTED]' Subject: Re: R: Query problem I see. Sorry I misunderstood you. You're stuck with a similar problem here, though. That is, you want to dynamically create columns (fields). Which can be done, of course, as long as you have the privileges and you're willing to divide up your query into at least 3. It sounds like you don't have privileges, though, right? In which case, you're stuck with the best possible scenario being: +---------+-------------+ | book | description | +---------+-------------+ | Title 1 | author | | Title 1 | publisher | | Title 2 | author | | Title 2 | publisher | +---------+-------------+ And so on... Needless to say, it's a shame that you don't have access to table administration. Those publishers and authors should be in different tables! Sorry I can't help more. Maybe someone with a bigger brain can help... Jed I liked it when Gigi Di Leo wrote this to me: > 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 CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html --------------------------------------------------------------------- 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
