List:General Discussion« Previous MessageNext Message »
From:Bill newton Date:November 19 2008 3:32pm
Subject:Re: Finding not quite duplicates
View as plain text  
Ok, I think I understand If there is a product that matches and is not 
discontinued it should only return that one.
I think I just learned about this trick from the mysql manual, although 
I can't seem to remember what section I found it in.

Basically you can join the table again, with the condition on the second 
join that its prod_discount must be lower than the first joins.
Then in the where statement require that the second join fail. So you'll 
only get one row returned that has the minimum value
 of prod_discont for a prod_pub_prod_id.

So something like this would work:

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
LEFT JOIN a as alias_for_a ON b.prod_pub_prod_id = 
alias_for_a.prod_pub_prod_id 
                    AND alias_for_a.prod_discont < a.prod_discont
WHERE
alias_for_a.prod_num IS NULL
ORDER BY b.prod_pub_prod_id;




US Data Export wrote:
>> -----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
>>     
>
>
>
>
>
>   


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