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

From: | Bill newton | Date: | November 18 2008 11:49pm |

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

View as plain text |

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; 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

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 |