List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 19 2008 8:32am
Subject:Re: Finding not quite duplicates
View as plain text  
> >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

Thread
Finding not quite duplicatesJerry Schwartz18 Nov
  • Re: Finding not quite duplicatesBill newton19 Nov
    • RE: Finding not quite duplicatesUS Data Export19 Nov
      • Re: Finding not quite duplicatesBill newton19 Nov
  • Re: Finding not quite duplicatesMartijn Tonies19 Nov
    • RE: Finding not quite duplicatesJerry Schwartz19 Nov
  • Re: Finding not quite duplicatesMartijn Tonies20 Nov