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]
