List:General Discussion« Previous MessageNext Message »
From:Bill newton Date:November 18 2008 11:49pm
Subject:Re: Finding not quite duplicates
View as plain text  
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;


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;


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