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