Re: Finding not quite duplicates
As a sidenote, your strings should be enclosed by single quotes, as per SQL standard, not double quotes, those are reserved for delimited identifiers. Hmmm. I've sort-of carried that over from the way I do PHP. I tend to use single quotes for strings that have no variables in them, so I use double-quotes around strings within the strings: $query = 'UPDATE foo SET field = always' That way I don't have to escape things, which I think makes them harder to read. I'll take your comment under advisement. Although that's true for string literals, you would still need to escape any user input since MySQL doesn't properly support parameters (or does it in 5.1 or so?) As I said, a complete side note :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding not quite duplicates
3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `1`, return discontinued. So the query that would accomplish that would be: SELECT IF(a.prod_num IS NOT NULL,IF(a.prod_discont = 0,a.prod_num,discontinued),) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id ORDER BY b.prod_pub_prod_id; [JS] If there were one row that had prod_discont = 1, and another that had prod_discont = 0, and they both had the same prod_pub_prod_id, wouldn't both rows be returned? That's not what I need. Given that prod_discont is an integer, can you use MAX/MIN to see if there's one? If there's none (in the JOIN), it will return NULL, right? As a sidenote, your strings should be enclosed by single quotes, as per SQL standard, not double quotes, those are reserved for delimited identifiers. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding not quite duplicates
Ok, I think I understand If there is a product that matches and is not discontinued it should only return that one. I think I just learned about this trick from the mysql manual, although I can't seem to remember what section I found it in. Basically you can join the table again, with the condition on the second join that its prod_discount must be lower than the first joins. Then in the where statement require that the second join fail. So you'll only get one row returned that has the minimum value of prod_discont for a prod_pub_prod_id. So something like this would work: SELECT IF(a.prod_num IS NOT NULL,IF(a.prod_discont = 0,a.prod_num,discontinued),) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id LEFT JOIN a as alias_for_a ON b.prod_pub_prod_id = alias_for_a.prod_pub_prod_id AND alias_for_a.prod_discont a.prod_discont WHERE alias_for_a.prod_num IS NULL ORDER BY b.prod_pub_prod_id; US Data Export wrote: -Original Message- From: Bill newton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2008 6:49 PM To: Jerry Schwartz Cc: 'mysql' Subject: Re: Finding not quite duplicates I'm having a little trouble with your naming. Im assuming the query you listed should be: SELECT IF(a.prod_num IS NOT NULL,a.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; [JS] That is correct, I apologize. I was trying make the changes as I typed, and obviously missed. And the third condition is unclear as to the condtion you want to match a.prod_discont , I'm guessing you mean 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `1`, return discontinued. So the query that would accomplish that would be: SELECT IF(a.prod_num IS NOT NULL,IF(a.prod_discont = 0,a.prod_num,discontinued),) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id ORDER BY b.prod_pub_prod_id; [JS] If there were one row that had prod_discont = 1, and another that had prod_discont = 0, and they both had the same prod_pub_prod_id, wouldn't both rows be returned? That's not what I need. You can nest IF statements in mysql queries. Hope it helps, Bill Jerry Schwartz wrote: As usual, my head is twisted around something that I think should be easy. Suppose I have a table, `a`, that has three fields: prod_num prod_pub_prod_id prod_discont I have another table, `b`, with a list of products of interest: prod_pub_prod_id What I need to do is match `b`.`prod_pub_prod_id` against table `a`.`prod_pub_prod_id` such that: 1) If there is no match at all, return . 2) If there is a match in `b` against a row in `b`, and `a`.`prod_discont` = 0, return `a`.`prod_num`. (There should never be two such rows.) 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `, return discontinued. 1) and 2) are very easily accomplished with a LEFT JOIN: SELECT IF(prod.prod_num IS NOT NULL,prod.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; The problem is with 3). It seems like I should be able to do this with one query, but I'm beginning to think that I need a separate query and a third table. Suggestions, anyone? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax
RE: Finding not quite duplicates
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 19, 2008 3:33 AM To: mysql@lists.mysql.com Subject: Re: Finding not quite duplicates [JS] If there were one row that had prod_discont = 1, and another that had prod_discont = 0, and they both had the same prod_pub_prod_id, wouldn't both rows be returned? That's not what I need. Given that prod_discont is an integer, can you use MAX/MIN to see if there's one? If there's none (in the JOIN), it will return NULL, right? [JS] I hadn't thought of that! I'll give it a hard look. As a sidenote, your strings should be enclosed by single quotes, as per SQL standard, not double quotes, those are reserved for delimited identifiers. Hmmm. I've sort-of carried that over from the way I do PHP. I tend to use single quotes for strings that have no variables in them, so I use double-quotes around strings within the strings: $query = 'UPDATE foo SET field = always' That way I don't have to escape things, which I think makes them harder to read. I'll take your comment under advisement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding not quite duplicates
As usual, my head is twisted around something that I think should be easy. Suppose I have a table, `a`, that has three fields: prod_num prod_pub_prod_id prod_discont I have another table, `b`, with a list of products of interest: prod_pub_prod_id What I need to do is match `b`.`prod_pub_prod_id` against table `a`.`prod_pub_prod_id` such that: 1) If there is no match at all, return . 2) If there is a match in `b` against a row in `b`, and `a`.`prod_discont` = 0, return `a`.`prod_num`. (There should never be two such rows.) 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `, return discontinued. 1) and 2) are very easily accomplished with a LEFT JOIN: SELECT IF(prod.prod_num IS NOT NULL,prod.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; The problem is with 3). It seems like I should be able to do this with one query, but I'm beginning to think that I need a separate query and a third table. Suggestions, anyone? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding not quite duplicates
I'm having a little trouble with your naming. Im assuming the query you listed should be: SELECT IF(a.prod_num IS NOT NULL,a.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; And the third condition is unclear as to the condtion you want to match a.prod_discont , I'm guessing you mean 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `1`, return discontinued. So the query that would accomplish that would be: SELECT IF(a.prod_num IS NOT NULL,IF(a.prod_discont = 0,a.prod_num,discontinued),) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id ORDER BY b.prod_pub_prod_id; You can nest IF statements in mysql queries. Hope it helps, Bill Jerry Schwartz wrote: As usual, my head is twisted around something that I think should be easy. Suppose I have a table, `a`, that has three fields: prod_num prod_pub_prod_id prod_discont I have another table, `b`, with a list of products of interest: prod_pub_prod_id What I need to do is match `b`.`prod_pub_prod_id` against table `a`.`prod_pub_prod_id` such that: 1) If there is no match at all, return . 2) If there is a match in `b` against a row in `b`, and `a`.`prod_discont` = 0, return `a`.`prod_num`. (There should never be two such rows.) 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `, return discontinued. 1) and 2) are very easily accomplished with a LEFT JOIN: SELECT IF(prod.prod_num IS NOT NULL,prod.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; The problem is with 3). It seems like I should be able to do this with one query, but I'm beginning to think that I need a separate query and a third table. Suggestions, anyone? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding not quite duplicates
-Original Message- From: Bill newton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2008 6:49 PM To: Jerry Schwartz Cc: 'mysql' Subject: Re: Finding not quite duplicates I'm having a little trouble with your naming. Im assuming the query you listed should be: SELECT IF(a.prod_num IS NOT NULL,a.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; [JS] That is correct, I apologize. I was trying make the changes as I typed, and obviously missed. And the third condition is unclear as to the condtion you want to match a.prod_discont , I'm guessing you mean 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `1`, return discontinued. So the query that would accomplish that would be: SELECT IF(a.prod_num IS NOT NULL,IF(a.prod_discont = 0,a.prod_num,discontinued),) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id ORDER BY b.prod_pub_prod_id; [JS] If there were one row that had prod_discont = 1, and another that had prod_discont = 0, and they both had the same prod_pub_prod_id, wouldn't both rows be returned? That's not what I need. You can nest IF statements in mysql queries. Hope it helps, Bill Jerry Schwartz wrote: As usual, my head is twisted around something that I think should be easy. Suppose I have a table, `a`, that has three fields: prod_num prod_pub_prod_id prod_discont I have another table, `b`, with a list of products of interest: prod_pub_prod_id What I need to do is match `b`.`prod_pub_prod_id` against table `a`.`prod_pub_prod_id` such that: 1) If there is no match at all, return . 2) If there is a match in `b` against a row in `b`, and `a`.`prod_discont` = 0, return `a`.`prod_num`. (There should never be two such rows.) 3) If there is a match in `b` against `a`, but the ONLY match is a row where `a`.`prod_discont` = `, return discontinued. 1) and 2) are very easily accomplished with a LEFT JOIN: SELECT IF(prod.prod_num IS NOT NULL,prod.prod_num,) as prod_num FROM b LEFT JOIN a ON b.prod_pub_prod_id = a.prod_pub_prod_id WHERE (a.prod_discont = 0 OR a.prod_discont IS NULL) ORDER BY b.prod_pub_prod_id; The problem is with 3). It seems like I should be able to do this with one query, but I'm beginning to think that I need a separate query and a third table. Suggestions, anyone? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
