From: Jerry Schwartz (jschwartzthe-infoshop.com)
Date: Mon Apr 14 2008 - 13:43:51 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

>From: Bill Newton [mailto:bnewtonnetworkmerchants.com]
>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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]