query problem

[auslander]

using mysql 4.0.x
please review the following sql then see below for the problem:
DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);
DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 customer_name varchar(100) default NULL,
 customer_fax varchar(100) default NULL,
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John 
Google','555-9999',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);

DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned NOT NULL default '0',
 department_id int(10) unsigned NOT NULL default '0',
 customer_name varchar(100) NOT NULL default '',
 customer_fax varchar(100) NOT NULL default '',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info_tmp (
 fax_id,
 barcode,
 document_id,
 department_id,
 customer_name,
 customer_fax)
SELECT
 a.fax_id,
 a.barcode,
 b.document_id,
 a.department_id,
 b.customer_name,
 b.customer_fax
FROM
 inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);
what i am attempting to do is create a 3rd table that contains all the 
info from inbound_fax_info plus a couple columns from outbound_fax_info 
based on the barcode column they both contain.  problem comes when the 
barcode is '' (blank).  barcodes can be blank in outbound and inbound 
(for specific reasons).  is there a way to write this INSERT INTO query 
where it will select the appropriate data from inbound and outbound if a 
barcode exists and insert into tmp inbound, otherwise just insert a new 
row with inbound data and defaults only in tmp inbound?

any help would be appreciated
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]