LEFT JOIN without temporary table?

["Jerry Schwartz" <jschwartz@xxxxxxxxxxxxxxxx>]

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/mysql?unsub=royale@xxxxxxxxxx