I want to get a list of all products that either exist or do not exist. In
other words, if prod.prod_pub_prod_id exists then I want to report its
prod_num; if it doesn't, then I want to report the product ID with a blank
value for the prod_num. I'm currently using a temporary table to do this,
but it feels inelegant to me. Is there any way to do this without a
temporary table?
Here's what I'm doing now:
DROP TEMPORARY TABLE IF EXISTS prod_exists;
CREATE TEMPORARY TABLE `giiexpr_db`.`prod_exists` (
`prod_pub_prod_id` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `prod_pub_prod_id` )
) ENGINE = MYISAM;
INSERT INTO prod_exists VALUES
("MCP-1018"),
("MCP-1024"),
...
("MCP-1031")
;
SELECT prod_exists.prod_pub_prod_id, IF(prod.prod_num IS NOT
NULL,prod.prod_num,"")
as GII_prod_ID
FROM prod_exists LEFT JOIN prod ON
prod_exists.prod_pub_prod_id = prod.prod_pub_prod_id
WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL)
ORDER BY prod_exists.prod_pub_prod_id;
This works fine, but is there a better way to do it?
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
