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]