> >3) If there is a match in `b` against `a`, but the ONLY match is a row
> >`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
> 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
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase
SQL Anywhere, Oracle & MS SQL Server
Database development questions? Check the forum!