RE: sql join help
Thanks Matt. But, what I am attempting to do with (oi.orderId IS NULL) is to eliminate the orders that have at least one order item with products of product parents not equal to 2. What you show finds all orders that have orderitems that have a product parent greater than 2, however, if that orderitem is in an order that also has a product parent of 1 or 2, I don't want to show that order. For example, I have these product Ids: 1 - hats - product parent 2 (clothes) 2 - shoes - product parent 2 (clothes) 3 - water pistols - product parent 3 (toys) 4 - rubber chickens - product parent 3 (toys) And order ids: 222 has: 1 and 3 product Ids, o.orderstatus =2 333 has: 3 and 4 product Ids , o.orderstatus =2 What you propose would find both order id 222 and 333 since both contain a product Id greater than 2. What I want is to only find those orders that do not have 1 or 2, in other words just order id 333 (from the two examples shown above). Help would be greatly appreciated! Michael At 11:10 AM +0100 4/11/04, Matt Chatterley wrote: Hmm. SELECT o.* FROM orders o INNER JOIN orderitems oi ON o.orderid = oi.orderid INNER JOIN products p ON p.productid = oi.productid AND p.productparentid > 2 WHERE o.orderstatus =2 Not sure why you are checking for a NULL ordered in orderitems? That would suggest you get back only items that have no associated order? The above should do what you state below, though - I think! Cheers, Matt -Original Message- From: Michael Collins [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 05:14 To: [EMAIL PROTECTED] Subject: sql join help I suppose this would be easier with subselects but I am using MySQL 4: I want all orders that are of orderStatus 2 and whose orderitems contain a product that is in a productparent category greater than 2. An orderitem can only have one product, and that product has a single certain product parent (defined in the products table). This is how the tables are related: members -< orders -< orderitems >- products >- productparents I have tried the following, but I know it is not correct: SELECT count(*) FROM orders AS o LEFT JOIN members AS m USING (memberId) LEFT JOIN orderItems AS oi ON (o.orderId=oi.orderId) LEFT JOIN products AS p ON (oi.productId=p.productId) AND (p.productParentId > 2) WHERE (oi.orderId IS NULL) AND (o.orderStatus=2); -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql join help
Hmm. SELECT o.* FROM orders o INNER JOIN orderitems oi ON o.orderid = oi.orderid INNER JOIN products p ON p.productid = oi.productid AND p.productparentid > 2 WHERE o.orderstatus =2 Not sure why you are checking for a NULL ordered in orderitems? That would suggest you get back only items that have no associated order? The above should do what you state below, though - I think! Cheers, Matt -Original Message- From: Michael Collins [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 05:14 To: [EMAIL PROTECTED] Subject: sql join help I suppose this would be easier with subselects but I am using MySQL 4: I want all orders that are of orderStatus 2 and whose orderitems contain a product that is in a productparent category greater than 2. An orderitem can only have one product, and that product has a single certain product parent (defined in the products table). This is how the tables are related: members -< orders -< orderitems >- products >- productparents I have tried the following, but I know it is not correct: SELECT count(*) FROM orders AS o LEFT JOIN members AS m USING (memberId) LEFT JOIN orderItems AS oi ON (o.orderId=oi.orderId) LEFT JOIN products AS p ON (oi.productId=p.productId) AND (p.productParentId > 2) WHERE (oi.orderId IS NULL) AND (o.orderStatus=2); -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql join help
I suppose this would be easier with subselects but I am using MySQL 4: I want all orders that are of orderStatus 2 and whose orderitems contain a product that is in a productparent category greater than 2. An orderitem can only have one product, and that product has a single certain product parent (defined in the products table). This is how the tables are related: members -< orders -< orderitems >- products >- productparents I have tried the following, but I know it is not correct: SELECT count(*) FROM orders AS o LEFT JOIN members AS m USING (memberId) LEFT JOIN orderItems AS oi ON (o.orderId=oi.orderId) LEFT JOIN products AS p ON (oi.productId=p.productId) AND (p.productParentId > 2) WHERE (oi.orderId IS NULL) AND (o.orderStatus=2); -- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USAhttp://michaelcollins.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql join help?
Multiple table update is only supported from 4.04 or above. If you are using 3.x, I think you should put the result into another table, delete MSI_List and rename the table to MSI_List. I would also very interested to know whether there exist a more elegant solution for 3.x. Best regards, > sql > > I have a zip code db named "MSI_Zipcodes that contains > > city > state > zipcode (index) > > I also have a db named "MSI_List" that contains > > ID (index) > email > city(empty) > state(empty) > zip > > My problem is: > > How can i bring the proper info from "MSI_Zipcodes"(city and state) > and enter it (city and state) into the empty fields of "MSI_List" in > accordance with the zip code in "MSI_List"? > > Thanks in advance! > > Todd Clemmer > > - > 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 > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu C&A Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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
sql join help?
sql I have a zip code db named "MSI_Zipcodes that contains city state zipcode (index) I also have a db named "MSI_List" that contains ID (index) email city(empty) state(empty) zip My problem is: How can i bring the proper info from "MSI_Zipcodes"(city and state) and enter it (city and state) into the empty fields of "MSI_List" in accordance with the zip code in "MSI_List"? Thanks in advance! Todd Clemmer - 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: SQL join help
I figured it out ... At 3:22 PM +0100 5/20/02, ds wrote: >On Mon, 2002-05-20 at 13:34, Ray Zimmerman wrote: >... > > For example, given the following data ... >> >> CREATE TABLE Object ( >> id int(11) NOT NULL auto_increment, >> PRIMARY KEY (id) >> ); >> >> INSERT INTO Object (id) VALUES (1); >> INSERT INTO Object (id) VALUES (2); >> INSERT INTO Object (id) VALUES (3); >> INSERT INTO Object (id) VALUES (4); >> INSERT INTO Object (id) VALUES (5); >> INSERT INTO Object (id) VALUES (6); >> >> CREATE TABLE Link ( >> parent int(11) NOT NULL, >> child int(11) NOT NULL, >> ); >> >> INSERT INTO Link (parent, child) VALUES (1, 4); >> INSERT INTO Link (parent, child) VALUES (1, 5); >> INSERT INTO Link (parent, child) VALUES (2, 4); >> INSERT INTO Link (parent, child) VALUES (2, 5); >> INSERT INTO Link (parent, child) VALUES (2, 6); >> INSERT INTO Link (parent, child) VALUES (3, 4); >> >> ... I want to find all Objects that have exactly 2 children with ids >> 4 and 5 (i.e. should match 1, but not 2 or 3) ... what's the query >> syntax? > >SELECT parent FROM Link WHERE child=4 OR child=5 >GROUP BY child HAVING count(*)=2; Actually, while this may work for the specific example, it doesn't work in general ... here's the query I finally found to work ... SELECT * FROM Object LEFT OUTER JOIN Link L0 ON (Object.id = L0.parent AND L0.child NOT IN (4,5)), Link L1, Link L2 WHERE L1.parent = Object.id AND L1.child = 4 AND L2.parent = Object.id AND L2.child = 5 AND L0.id IS NULL; > > How about if I want to find all Objects which have no children (i.e. >> should match 4, 5 and 6, but not 1, 2 or 3). > >SELECT id FROM Object LEFT JOIN Link ON (Object.id=Link.parent) >WHERE Link.parent IS NULL; And yes, this is basically what I came up with here too. Thanks. -- Ray Zimmerman / e-mail: [EMAIL PROTECTED] / 428-B Phillips Hall Sr Research / phone: (607) 255-9645 / Cornell University Associate / FAX: (815) 377-3932 / Ithaca, NY 14853 - 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: SQL join help
On Mon, 2002-05-20 at 13:34, Ray Zimmerman wrote: ... > For example, given the following data ... > >CREATE TABLE Object ( > id int(11) NOT NULL auto_increment, > PRIMARY KEY (id) >); > >INSERT INTO Object (id) VALUES (1); >INSERT INTO Object (id) VALUES (2); >INSERT INTO Object (id) VALUES (3); >INSERT INTO Object (id) VALUES (4); >INSERT INTO Object (id) VALUES (5); >INSERT INTO Object (id) VALUES (6); > >CREATE TABLE Link ( > parent int(11) NOT NULL, > child int(11) NOT NULL, >); > >INSERT INTO Link (parent, child) VALUES (1, 4); >INSERT INTO Link (parent, child) VALUES (1, 5); >INSERT INTO Link (parent, child) VALUES (2, 4); >INSERT INTO Link (parent, child) VALUES (2, 5); >INSERT INTO Link (parent, child) VALUES (2, 6); >INSERT INTO Link (parent, child) VALUES (3, 4); > > ... I want to find all Objects that have exactly 2 children with ids > 4 and 5 (i.e. should match 1, but not 2 or 3) ... what's the query > syntax? SELECT parent FROM Link WHERE child=4 OR child=5 GROUP BY child HAVING count(*)=2; > How about if I want to find all Objects which have no children (i.e. > should match 4, 5 and 6, but not 1, 2 or 3). SELECT id FROM Object LEFT JOIN Link ON (Object.id=Link.parent) WHERE Link.parent IS NULL; Hope this simple queries solves your problem. -- dsoares (sql) - 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
SQL join help
Dear list, I've done basic SQL for a number of years, but I recently came up against a query I can't quite seem to figure out the join syntax for. Suppose I have rows in an Object table which are linked in a hierarchy by the entries in a Link table and I want to find all Objects which have given set of Objects as children. For example, given the following data ... CREATE TABLE Object ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id) ); INSERT INTO Object (id) VALUES (1); INSERT INTO Object (id) VALUES (2); INSERT INTO Object (id) VALUES (3); INSERT INTO Object (id) VALUES (4); INSERT INTO Object (id) VALUES (5); INSERT INTO Object (id) VALUES (6); CREATE TABLE Link ( parent int(11) NOT NULL, child int(11) NOT NULL, ); INSERT INTO Link (parent, child) VALUES (1, 4); INSERT INTO Link (parent, child) VALUES (1, 5); INSERT INTO Link (parent, child) VALUES (2, 4); INSERT INTO Link (parent, child) VALUES (2, 5); INSERT INTO Link (parent, child) VALUES (2, 6); INSERT INTO Link (parent, child) VALUES (3, 4); ... I want to find all Objects that have exactly 2 children with ids 4 and 5 (i.e. should match 1, but not 2 or 3) ... what's the query syntax? How about if I want to find all Objects which have no children (i.e. should match 4, 5 and 6, but not 1, 2 or 3). TIA, -- Ray Zimmerman / e-mail: [EMAIL PROTECTED] / 428-B Phillips Hall Sr Research / phone: (607) 255-9645 / Cornell University Associate / FAX: (815) 377-3932 / Ithaca, NY 14853 - 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
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
