>-----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. I"m 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]
