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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
