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]
