I've found yet another oddity with this situation. If I leave the date test
off of both JOINs they give the same number of rows, but they give me the
wrong number! Neither one of them gives me 860 rows returned. I must not
understand how a LEFT JOIN works.
By the way, the EXPLAIN for both of my original queries is the same:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: eo_name_table
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 860
Extra: Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: prod_title,prod_title_fulltext
key: prod_title
key_len: 766
ref: giiexpr_db.eo_name_table.eo_name
rows: 1
Extra: Using where
2 rows in set (0.05 sec)
>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]
