What is wrong with me today?!?! I explained myself incorrectly: a) information from outbound_fax_info where the barcodes DO match.
Sorry all!! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] To: auslander <[EMAIL PROTECTED]> 06/30/2004 02:22 cc: [EMAIL PROTECTED] PM Fax to: Subject: Re: query problem Eliminate the rows from outbound_fax_info where the barcode is blank. The result of the JOIN will be all of the rows of inbound_fax_info matched up to: a) information form outbound_fax_info except where the barcodes match b) blank columns where the barcodes didn't match. Use the COALESCE() function (it returns the first non-null value from a list of values) and you gain the ability to replace missing values with something else. 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, COALESCE(b.customer_name,'no customer') COALESCE(b.customer_fax,'no customer fax') FROM inbound_fax_info a LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode) AND b.barcode > '' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine auslander <[EMAIL PROTECTED] To: [EMAIL PROTECTED] ay.rr.com> cc: Fax to: 06/30/2004 01:50 Subject: query problem PM 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]