> >As a sidenote, your strings should be enclosed by single quotes, as per
> >SQL standard, not double quotes, those are reserved for delimited
> >identifiers.
> >
> Hmmm. I've sort-of carried that over from the way I do PHP. I tend to use
> single quotes for strings that have no variables in them,
>-Original Message-
>From: Martijn Tonies [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, November 19, 2008 3:33 AM
>To: mysql@lists.mysql.com
>Subject: Re: Finding not quite duplicates
>
>> [JS] If there were one row that had prod_discont = 1, and another that
.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:[EMAIL PROTECTED]
Sent: Tuesday, November 18, 2008 6:49 PM
To: Jerry Schwartz
Cc: 'mysql'
Subject: Re: Findin
> >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
>-Original Message-
>From: Bill newton [mailto:[EMAIL PROTECTED]
>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 assumin
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;
