Hi Jerry,
I think the problem is that NULL is not less than or greater than your
prod_published date. So you probably have eo_pub_date set to NULL in 56
of your rows.
so for
eo_name_table.eo_pub_date > prod.prod_published
or
eo_name_table.eo_pub_date <= prod.prod_published
mysql will rerturn false if eo_name_table.eo_pub_date is NULL for
either test.
Jerry Schwartz wrote:
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]
