RE: Probably OT - SQL join help needed
Hi Robert - Not an MySQL expert, but, I think what you are describing is a perfect use of MERGE tables. They take up basically no space (except to contain the names of the tables that are merged) and allow for you to merge these two tables logically and then just do whatever queries you want against them. Regards, Ken Hylton -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 2:51 AM To: [EMAIL PROTECTED]; Robert Cross Subject:Re: Probably OT - SQL join help needed Hello Robert, > Hello experts, I've got a small problem with an sql query here that's got > me completely stuck. > > In my MySQL database I've got two tables here that have identical design, > e.g. > table 'detail' - columns sales-order, quantity, part-number, price, > date-sent > and > table 'archived' - columns sales-order, quantity, part-number, price, > date-sent. > > Detail is for 'active' orders, and Archived is for fulfilled orders. > > Now some genius here wants to see all the records that reference a > particular part number, > irrespective of whether in archived or detail. > > My current approach is to create a temporary table with all the suitable > records from > detail, add in any suitable records from archived, and then do a select * > query from this > temporary table, before dropping it. > > Now it strikes me that this isn't a very smart way to do this, and it's > probably achievable > via joins but, try as I might, I can't get the system to do it. Anyone got > any bright ideas/suggestions? If the tables are identical then UNION may be what you're looking for: 6.4.1.2 UNION Syntax (MySQL >4.0). For every user "genius" who forgets to put something in the spec up-front, there's a computer guy who's smarter! Regards, =dn - 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
Probably OT - SQL join help needed
Hello experts, I've got a small problem with an sql query here that's got me completely stuck. In my MySQL database I've got two tables here that have identical design, e.g. table 'detail' - columns sales-order, quantity, part-number, price, date-sent and table 'archived' - columns sales-order, quantity, part-number, price, date-sent. Detail is for 'active' orders, and Archived is for fulfilled orders. Now some genius here wants to see all the records that reference a particular part number, irrespective of whether in archived or detail. My current approach is to create a temporary table with all the suitable records from detail, add in any suitable records from archived, and then do a select * query from this temporary table, before dropping it. Worst still, I'm going to have to do this all via PHP :-( Now it strikes me that this isn't a very smart way to do this, and it's probably achievable via joins but, try as I might, I can't get the system to do it. Anyone got any bright ideas/suggestions? Regards Bob Cross. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This message is confidential. It may also be privileged or protected by other legal rules. It does not constitute an offer or acceptance of an offer, nor shall it form any part of a legally binding contract. If you have received this communication in error, please let us know by reply then destroy it. You should not use, print, copy the message or disclose its contents to anyone. E-mail is subject to possible data corruption, is not secure, and its content does not necessarily represent the opinion of this Company. No representation or warranty is made as to the accuracy or completeness of the information and no liability can be accepted for any loss arising from its use. This e-mail and any attachments are not guaranteed to be free from so-called computer viruses and it is recommended that you check for such viruses before down-loading it to your computer equipment. This Company has no control over other websites to which there may be hypertext links and no liability can be accepted in relation to those sites. Scottish & Newcastle plc Registered in Scotland, Registered Number 16288 Registered Office: 33, Ellersly Road, Edinburgh, EH12 6HX * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * - 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
Re: Probably OT - SQL join help needed
Hello Robert, > Hello experts, I've got a small problem with an sql query here that's got > me completely stuck. > > In my MySQL database I've got two tables here that have identical design, > e.g. > table 'detail' - columns sales-order, quantity, part-number, price, > date-sent > and > table 'archived' - columns sales-order, quantity, part-number, price, > date-sent. > > Detail is for 'active' orders, and Archived is for fulfilled orders. > > Now some genius here wants to see all the records that reference a > particular part number, > irrespective of whether in archived or detail. > > My current approach is to create a temporary table with all the suitable > records from > detail, add in any suitable records from archived, and then do a select * > query from this > temporary table, before dropping it. > > Now it strikes me that this isn't a very smart way to do this, and it's > probably achievable > via joins but, try as I might, I can't get the system to do it. Anyone got > any bright ideas/suggestions? If the tables are identical then UNION may be what you're looking for: 6.4.1.2 UNION Syntax (MySQL >4.0). For every user "genius" who forgets to put something in the spec up-front, there's a computer guy who's smarter! Regards, =dn - 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
Probably OT - SQL join help needed
Hello experts, I've got a small problem with an sql query here that's got me completely stuck. In my MySQL database I've got two tables here that have identical design, e.g. table 'detail' - columns sales-order, quantity, part-number, price, date-sent and table 'archived' - columns sales-order, quantity, part-number, price, date-sent. Detail is for 'active' orders, and Archived is for fulfilled orders. Now some genius here wants to see all the records that reference a particular part number, irrespective of whether in archived or detail. My current approach is to create a temporary table with all the suitable records from detail, add in any suitable records from archived, and then do a select * query from this temporary table, before dropping it. Now it strikes me that this isn't a very smart way to do this, and it's probably achievable via joins but, try as I might, I can't get the system to do it. Anyone got any bright ideas/suggestions? Regards Bob Cross. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This message is confidential. It may also be privileged or protected by other legal rules. It does not constitute an offer or acceptance of an offer, nor shall it form any part of a legally binding contract. If you have received this communication in error, please let us know by reply then destroy it. You should not use, print, copy the message or disclose its contents to anyone. E-mail is subject to possible data corruption, is not secure, and its content does not necessarily represent the opinion of this Company. No representation or warranty is made as to the accuracy or completeness of the information and no liability can be accepted for any loss arising from its use. This e-mail and any attachments are not guaranteed to be free from so-called computer viruses and it is recommended that you check for such viruses before down-loading it to your computer equipment. This Company has no control over other websites to which there may be hypertext links and no liability can be accepted in relation to those sites. Scottish & Newcastle plc Registered in Scotland, Registered Number 16288 Registered Office: 33, Ellersly Road, Edinburgh, EH12 6HX * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * - 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
