As usual, the computer is right and I am wrong. The only reason that one query was coming out "right" is that it just happened the WHERE clause was never failing. It was just luck that my data was just so.
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 >-----Original Message----- >From: Jerry Schwartz [mailto:[EMAIL PROTECTED] >Sent: Monday, April 14, 2008 11:26 AM >To: 'Mysql' >Subject: LEFT JOIN problem > >I have a table, eo_name_table, that has exactly 860 unique titles in it. >Each record also has a date field, eo_pub_date: > >+-------------+--------------+------+-----+---------+-------+ >| Field | Type | Null | Key | Default | Extra | >+-------------+--------------+------+-----+---------+-------+ >| eo_name | varchar(255) | | PRI | | | >| eo_pub_date | date | YES | | NULL | | >+-------------+--------------+------+-----+---------+-------+ > >I have another table, prod, that has many fields in it but the fields of >interest are prod_num, prod_title, prod_discont, and prod_published. The >other fields are irrelevant Here is the structure of the prod table: > >+---------------------+-----------------------+------+-----+---------+-- >---- >-+ >| Field | Type | Null | Key | Default | >Extra >| >+---------------------+-----------------------+------+-----+---------+-- >---- >-+ >| prod_id | varchar(15) | | PRI | | >| >| prod_num | mediumint(6) unsigned | YES | MUL | NULL | >| >| prod_title | varchar(255) | YES | MUL | NULL | >| >| prod_type | varchar(2) | YES | | NULL | >| >| prod_vat_pct | decimal(5,2) | YES | | NULL | >| >| prod_discont | tinyint(1) | YES | | NULL | >| >| prod_ready | tinyint(1) | YES | | NULL | >| >| pub_id | varchar(15) | YES | MUL | NULL | >| >| prod_published | date | YES | | NULL | >| >| prod_pub_prod_id | varchar(255) | YES | MUL | NULL | >| >| prod_pub_acct_id | varchar(2) | YES | | NULL | >| >| prod_pub_doi | date | YES | | NULL | >| >| prod_pub_resp | date | YES | | NULL | >| >| prod_pub_prod_url | varchar(255) | YES | | NULL | >| >| prod_rel_freq | smallint(3) | YES | | NULL | >| >| prod_content_info | varchar(255) | YES | | NULL | >| >| prod_info_type | varchar(5) | YES | | NULL | >| >| prod_language | varchar(50) | YES | | NULL | >| >| prod_broch_doc | varchar(255) | YES | | NULL | >| >| prod_samp_doc | varchar(255) | YES | | NULL | >| >| prod_samp_pgs | varchar(255) | YES | | NULL | >| >| prod_exec_summ | varchar(255) | YES | | NULL | >| >| prod_toc_doc | varchar(255) | YES | | NULL | >| >| prod_e_title_tag | varchar(255) | YES | | NULL | >| >| prod_meta_tags | varchar(255) | YES | | NULL | >| >| prod_keywords | varchar(255) | YES | | NULL | >| >| prod_comments | text | YES | | NULL | >| >| prod_if_sample_pdf | varchar(255) | YES | | NULL | >| >| prod_stop_date | date | YES | | NULL | >| >| prod_hide_web | tinyint(1) | YES | | NULL | >| >| prod_changed | tinyint(1) | YES | | NULL | >| >| prod_export | tinyint(1) | YES | | NULL | >| >| prod_export_pending | tinyint(1) | YES | | NULL | >| >| prod_scoop_changed | tinyint(1) | YES | | NULL | >| >| prod_on_scoop | tinyint(1) | YES | | NULL | >| >| prod_added | datetime | YES | | NULL | >| >| prod_updated | datetime | YES | | NULL | >| >+---------------------+-----------------------+------+-----+---------+-- >---- >-+ > >I am trying to break eo_name_table into two sets, based upon matching >eo_name_table.eo_pub_date against prod.prod_published. The first query >is > >SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, "") AS >pub_date, > IF(prod.prod_num IS NOT NULL, prod.prod_num, "") AS prod_num, > IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, "", >"Discontinued") AS discont, > IF(prod.prod_title IS NOT NULL, prod.prod_title, "") AS >match_title >FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = >prod.prod_title > > AND eo_name_table.eo_pub_date <= prod.prod_published >WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) >ORDER BY eo_name_table.eo_name; > >As expected, this gives me exactly 860 rows in the result because the >left >join should give me (at least) one result row for each row in >eo_name_table. >Some of these rows, of course, have "" values for every field. > >Now I want to find the inverse set: > >SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, "") AS >pub_date, > IF(prod.prod_num IS NOT NULL, prod.prod_num, "") AS prod_num, > IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, "", >"Discontinued") AS discont, > IF(prod.prod_title IS NOT NULL, prod.prod_title, "") AS >match_title >FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = >prod.prod_title > > AND eo_name_table.eo_pub_date > prod.prod_published >WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) >ORDER BY eo_name_table.eo_name; > >Instead of getting 860 rows in the result, I get 804 rows. > >I've been wrestling with this for days. What am I missing? > > >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/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
