Re: query problem

[auslander]

Actually, i figured it out.  don't know why it was so hard to see it.  
all i did was change:

LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode)
to:
LEFT JOIN outbound_fax_info b on (b.barcode != '' and ucase(a.barcode = 
b.barcode).

if barcode was blank in outbound (b) then the data was skipped and only 
the data from inbound (a) was inserted into the new table.

thanks for your assistance tho. much appreciated.
[EMAIL PROTECTED] wrote:
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]