store | blogs | forums | twitter | facebook | wiki | downloads | support portal
Atomic Secure Linux
It is currently Tue Aug 20, 2019 12:14 pm

» Feed - Atomicorp

All times are UTC - 5 hours [ DST ]




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: [SOLVED] Query returns varying result sets
Unread postPosted: Sun Nov 09, 2014 11:10 am 
Offline
Forum User
Forum User

Joined: Sat Nov 08, 2014 4:35 pm
Posts: 5
Location: Georgia, USA
I am using MariaDB on debian/gnu/linux/stable. I have a database with three tables.

Persons:
Code:
+--------------------+-------------+------+-----+---------+-------+
| 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:
+----------------------+-------------+------+-----+---------+-------+
| 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:
+---------+-------------+------+-----+---------+----------------+
| 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:
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.

Top
 Profile  
Reply with quote  
 Post subject: Re: Query returns varying result sets
Unread postPosted: Sun Nov 09, 2014 6:44 pm 
Offline
Forum User
Forum User

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Query returns varying result sets
Unread postPosted: Sun Nov 09, 2014 6:59 pm 
Offline
Forum User
Forum User

Joined: Sat Nov 08, 2014 4:35 pm
Posts: 5
Location: Georgia, USA
To be more clear, it is the inner join process
Code:
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:
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     |
+----+------+---------+


Top
 Profile  
Reply with quote  
 Post subject: Re: Query returns varying result sets
Unread postPosted: Mon Nov 10, 2014 2:17 pm 
Offline
Forum User
Forum User

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Query returns varying result sets
Unread postPosted: Mon Nov 10, 2014 3:13 pm 
Offline
Forum User
Forum User

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


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

» Feed - Atomicorp

All times are UTC - 5 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group