>From: Bill Newton [mailto:[EMAIL PROTECTED] >Sent: Monday, April 14, 2008 2:09 PM >To: Jerry Schwartz >Cc: 'Mysql' >Subject: Re: LEFT JOIN problem > >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. [JS] I wish it were that simple. There are no rows in eo_name_table where eo_pub_date is NULL.
> >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. [JS] But it evidently does not. The second one works perfectly, the one above does not. > > > > >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]
