List: | General Discussion | « Previous MessageNext Message » | |

From: | Bill newton | Date: | November 19 2008 3:32pm |

Subject: | Re: Finding not quite duplicates | ||

View as plain text |

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:bnewton@stripped] >> 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 >> > > > > > > -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax

Thread | ||
---|---|---|

• Finding not quite duplicates | Jerry Schwartz | 18 Nov |

• Re: Finding not quite duplicates | Bill newton | 19 Nov |

• RE: Finding not quite duplicates | US Data Export | 19 Nov |

• Re: Finding not quite duplicates | Bill newton | 19 Nov |

• Re: Finding not quite duplicates | Martijn Tonies | 19 Nov |

• RE: Finding not quite duplicates | Jerry Schwartz | 19 Nov |

• Re: Finding not quite duplicates | Martijn Tonies | 20 Nov |