From: Martijn Tonies
Date: November 19 2008 8:32am
Subject: Re: Finding not quite duplicates
List-Archive: http://lists.mysql.com/mysql/215273
Message-Id: <00d701c94a21$6a641590$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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