[SOLVED] Query returns varying result sets

Support/Development for MySQL, MariaDB, and other database systems
jheaton5
Forum User
Forum User
Posts: 5
Joined: Sat Nov 08, 2014 4:35 pm
Location: Georgia, USA

[SOLVED] Query returns varying result sets

Unread post by jheaton5 »

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

Persons:

Code: Select all

+--------------------+-------------+------+-----+---------+-------+
| 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?
Last edited by jheaton5 on Mon Nov 10, 2014 3:14 pm, edited 1 time in total.
jheaton5
Forum User
Forum User
Posts: 5
Joined: Sat Nov 08, 2014 4:35 pm
Location: Georgia, USA

Re: Query returns varying result sets

Unread post by jheaton5 »

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.
jheaton5
Forum User
Forum User
Posts: 5
Joined: Sat Nov 08, 2014 4:35 pm
Location: Georgia, USA

Re: Query returns varying result sets

Unread post by jheaton5 »

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     |
+----+------+---------+
jheaton5
Forum User
Forum User
Posts: 5
Joined: Sat Nov 08, 2014 4:35 pm
Location: Georgia, USA

Re: Query returns varying result sets

Unread post by jheaton5 »

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.
jheaton5
Forum User
Forum User
Posts: 5
Joined: Sat Nov 08, 2014 4:35 pm
Location: Georgia, USA

Re: Query returns varying result sets

Unread post by jheaton5 »

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.
Post Reply