Atomicorp

Posted: **Sun Nov 09, 2014 11:10 am**

I am using MariaDB on debian/gnu/linux/stable. I have a database with three tables.

Persons:

+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| record_number      | int(10)     | NO   | PRI | NULL    |       |
| surname            | varchar(20) | YES  |     | NULL    |       |
| given_name         | varchar(30) | YES  |     | NULL    |       |
| sex                | varchar(1)  | YES  |     | NULL    |       |
| fathers_rn         | int(10)     | YES  |     | NULL    |       |
| mothers_rn         | int(10)     | YES  |     | NULL    |       |
| birth_date_prefix  | varchar(20) | YES  |     | NULL    |       |
| birth_year         | varchar(10) | YES  |     | NULL    |       |
| birth_month        | varchar(10) | YES  |     | NULL    |       |
| birth_day          | varchar(10) | YES  |     | NULL    |       |
| birth_city         | varchar(20) | YES  |     | NULL    |       |
| birth_county       | varchar(20) | YES  |     | NULL    |       |
| birth_state        | varchar(20) | YES  |     | NULL    |       |
| death_date_prefix  | varchar(20) | YES  |     | NULL    |       |
| death_year         | varchar(10) | YES  |     | NULL    |       |
| death_month        | varchar(10) | YES  |     | NULL    |       |
| death_day          | varchar(10) | YES  |     | NULL    |       |
| death_city         | varchar(20) | YES  |     | NULL    |       |
| death_county       | varchar(20) | YES  |     | NULL    |       |
| death_state        | varchar(20) | YES  |     | NULL    |       |
| burial_date_prefix | varchar(20) | YES  |     | NULL    |       |
| burial_year        | varchar(10) | YES  |     | NULL    |       |
| burial_month       | varchar(10) | YES  |     | NULL    |       |
| burial_day         | varchar(10) | YES  |     | NULL    |       |
| burial_city        | varchar(20) | YES  |     | NULL    |       |
| burial_county      | varchar(20) | YES  |     | NULL    |       |
| burial_state       | varchar(20) | YES  |     | NULL    |       |
| notes              | text        | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+

There are 525 records in this table

Marriages:

Code: Select all

+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| marriage_number      | int(10)     | NO   | PRI | NULL    |       |
| husbands_rn          | int(10)     | YES  |     | NULL    |       |
| wifes_rn             | int(10)     | YES  |     | NULL    |       |
| marriage_date_prefix | varchar(20) | YES  |     | NULL    |       |
| marriage_year        | varchar(10) | YES  |     | NULL    |       |
| marriage_month       | varchar(10) | YES  |     | NULL    |       |
| marriage_day         | varchar(10) | YES  |     | NULL    |       |
| marriage_city        | varchar(20) | YES  |     | NULL    |       |
| marriage_county      | varchar(20) | YES  |     | NULL    |       |
| marriage_state       | varchar(20) | YES  |     | NULL    |       |
| narrative            | text        | YES  |     | NULL    |       |
+----------------------+-------------+------+-----+---------+-------+

There are 100 records in this table.

Months:

Code: Select all

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(10)     | NO   | PRI | NULL    | auto_increment |
| mo_n    | varchar(10) | YES  |     | NULL    |                |
| mo_name | varchar(10) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

Here is my query:

Code: Select all

set @rn = <int in 1-525>; 
select record_number, given_name, surname, concat (mo_name,' ', birth_day,', ', birth_year) 
     from persons 
     inner join months on persons.birth_month=months.mo_n 
     where record_number in (
               select fathers_rn 
               from persons 
               where record_number = @rn
               ) 
          or record_number in (
               select mothers_rn 
               from persons 
               where record_number = @rn);

The query involves two tables, persons and months. The variable @rn is the search target. The query always resolves the inner join statement. As long as @rn < 99 I get an accurate result of a Mother and Father for the target person. When @rn = 99 returns the Mother's record but not the Father's even though the Father's information is populated in record 99. If @rn > 99 the query returns a null set even though the Father's and Mother's Father's information is populated in the target person's record.

Does anyone have any ideas what may be happening here?

Posted: **Sun Nov 09, 2014 6:44 pm**

After considerable testing of all portions of my query and an examination of the data in table persons, I have come to the conclusion that the offending expression is

Code: Select all

concat (mo_name,' ', birth_day,', ', birth_year)

A null set is returned when any of the elements in this expression are null. Therefore if the value of 'birth_month' is null for a target person the whole query returns a null set. Seems like I read of a solution to this in the documentation. I'll do more research. Thanks for your patience.

Posted: **Sun Nov 09, 2014 6:59 pm**

To be more clear, it is the inner join process

Code: Select all

concat(mo_name,' ', birth_day,', ', birth_year) from persons inner join months on persons.birth_month=months.mo_n

that fails to find a value in table months if the birth_month = 0 or NULL. Here is the entire table months:

Code: Select all

MariaDB [ae]> select * from months;
+----+------+---------+
| id | mo_n | mo_name |
+----+------+---------+
|  1 | 1    | Jan     |
|  2 | 2    | Feb     |
|  3 | 3    | Mar     |
|  4 | 4    | Apr     |
|  5 | 5    | May     |
|  6 | 6    | Jun     |
|  7 | 7    | Jul     |
|  8 | 8    | Aug     |
|  9 | 9    | Sep     |
| 10 | 10   | Oct     |
| 11 | 11   | Nov     |
| 12 | 12   | Dec     |
+----+------+---------+

Posted: **Mon Nov 10, 2014 2:17 pm**

The concat statement works so that is not the problem.

The problem is that the query does not test for a null value in the fathers_rn and mothers_rn. I have tried several methods for testing for a null value in both fathers_rn and mothers_rn so that I get a result set if one or the other is populated. I have tried changing = to <=> which does not work. I have tried if statements and I can't figure out where to put the if statement to make it work. Here is what I have so far

Code: Select all

select record_number, given_name, surname, 
     concat_ws(mo_name,' ', birth_day,', ', birth_year) 
     from persons 
     inner join months on persons.birth_month = months.mo_n 
          if fathers_rn not null then 
          where
               record_number in (
               select fathers_rn
               from persons 
               where record_number = @rn)
          end if
     or 
          if mothers_rn not null then 
          where
               record_number in (
               select mothers_rn 
               from persons 
               where record_number = @rn)
          else
              'Mother not found'
          end if;

which gives an error message on the if statement. I have tried putting the if statement after the where command which did not work. I have tried putting the if statement within the in() function, which doesn't work either.

I'm ready to start from scratch:
What I am looking to do is list the father and mother of a person. When there is a value in either father or mother I want a listing of what is there. Perhaps I need to change change all the null values to empty strings and alter the table to not null with default values of 0 and empty string.

Posted: **Mon Nov 10, 2014 3:13 pm**

I have a workable solution:
I altered my tables to that int columns have a default value of 0 and varchar columns have no default value. All columns are set to null=none.
I added a record in months so that m_no = N/A and m_name = N/A. In persons I updated every record where the month fields had no value to 'N/A'.

Now my original query works in all cases.

Atomicorp

[SOLVED] Query returns varying result sets

[SOLVED] Query returns varying result sets

Re: Query returns varying result sets

Re: Query returns varying result sets

Re: Query returns varying result sets

Re: Query returns varying result sets