Adding in children to MLS report

This goes along with my previous post on extracting the MLS data to create a usable phone listing.  This takes that and adds in the children's names and birthdays.  Note:  you really need Perl and cygwin installed on your Windows machine.  Seriously.

1.  mlsdirectory.sql
select * from
(
    SELECT HH.HofHID, HH.PreferredName "Name", S.PreferredName "SpouseName", HH.Street1, HH.HouseholdPhone, HH.IndividualPhone "CellPhone", S.IndividualPhone "SpouseCellPhone"
    FROM mls.membership HH,
         mls.membership S
    where HH.HHPosition in ('Head of Household','Spouse')
    and HH.HofHID = S.HofHID
    and S.HHPosition = 'Spouse'
    UNION
    Select HofHID, PreferredName "Name", NULL "SpouseName", Street1, HouseHoldPhone, IndividualPhone "CellPhone", NULL "SpouseCellPhone"
    from mls.membership
    where married = 'Single'
    and HHPosition = 'Head of Household'
) PhoneListing
where Name <> SpouseName
or SpouseName is null
order by Name;

2.  mlschildren.sql
Select HofHID, PreferredName, Birth
    from mls.membership
where HHPosition = 'Other';
 
3.  mlsdirectory.pl
open (MLS, 'c:\temp\mlsdirectory.out');
open (TSV, '>c:\temp\mlsdirectory.tsv');
while (<MLS>)
{
    chomp;
    ($HofHID, $HofHNAME, $SpouseNAME, $address, $homephone, $cellphone, $spousephone) = split("\t");
    print (TSV $HofHNAME);
    print (TSV "\t");
    print (TSV $SpouseNAME);
    print (TSV "\t");
    print (TSV $address);
    print (TSV "\t");
    print (TSV $homephone);
    print (TSV "\t");
    print (TSV $cellphone);
    print (TSV "\t");
    print (TSV $spousename);
    print (TSV "\n");
    open (CHILD, 'c:\temp\mlschildren.out');
    while (<CHILD>)
    {
        chomp;
        ($CofHID, $CofPNAME, $cbirth) = split("\t");
        if ($CofHID == $HofHID)
        {
            print (TSV $CofPNAME);
            print (TSV "\t");
            print (TSV $cbirth);
            print (TSV "\n");
        }
    }
    close (CHILD);
}
close (MLS);
close (TSV);

4.  mlsdirectory.bat
d:
cd \MySQL\MySQL Server 5.5\bin

mysql -uroot -pxxxxxx < c:\scripts\mlsdirectory.sql > c:\temp\mlsdirectory.out
mysql -uroot -pxxxxxx < c:\scripts\mlschildren.sql > c:\temp\mlschildren.out

c:
cd\scripts
perl mlsdirectory.pl

This creates a tab delimited file.