List:General Discussion« Previous MessageNext Message »
From:US Data Export Date:November 19 2008 12:10am
Subject:RE: Finding not quite duplicates
View as plain text  

>-----Original Message-----
>From: Bill newton [mailto:bnewton@stripped]
>Sent: Tuesday, November 18, 2008 6:49 PM
>To: Jerry Schwartz
>Cc: 'mysql'
>Subject: Re: Finding not quite duplicates
>
>I'm having a little trouble with your naming. I"m assuming the query you
>listed should be:
>
>SELECT IF(a.prod_num IS NOT NULL,a.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;
>
[JS] That is correct, I apologize. I was trying make the changes as I typed,
and obviously missed.
>
>And  the third condition is unclear as to the condtion you want to match
>a.prod_discont , I'm guessing you mean
>
>
>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.
>
>You can nest IF statements in mysql queries.
>
>Hope it helps,
>
>Bill
>
>Jerry Schwartz wrote:
>> 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
>>
>>
>>
>>
>>
>>
>>
>
>
>--
>Bill Newton
>Network Merchants Inc.
>http://www.nmi.com
>(847) 352-4850 ext 141/ Tel
>(888) 829-3631/ Fax




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