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