[Mysql-list] selecting over multiple tables

[Vicki Stanfield]

I have created a database which contains several tables of information
about my family. I need to be able to search for upcoming birthdays. I
have been able to implement a scheme whereby the user select a specific
branch of the family (which maps to a table) and the code works for that.
However, I would much prefer to search multiple tables at the same time.
My ideal solution would be to have a field in each table that denotes it
as a familysearch table and then have a function which searches all of
those tables for birthdays coming up within the next 30 days. I have not
been able to implement my ideal solution. ;-) My secondary solution would
be to do a multiple join thing to pull out a list of birthdays coming up
within the next 30 days. Here is what I have so far:

<?
if (!($link = mysql_connect("localhost", "vicki", "password")))
        die("Cannot connect");

mysql_select_db("family");

$result = mysql_query("select nickname,birthdate from Table1
where concat(substring(now(),1,4),substring(birthdate,5,6))
between now() and date_add(now(), interval 30 day) UNION
select nickname,birthdate from Table2
where concat(substring(now(),1,4),substring(birthdate,5,6))
between now() and date_add(now(), interval 30 day)");

    while($line = mysql_fetch_array($result))
    {
        echo "name: " . $line["nickname"] . "<br>";
        echo "birthdate: " . $line["birthdate"] . "<br>";
    }
?>

My problems are these:
1.) Doesn't seem to select any birthdays where the birthyear is not the
current year.

2.) unsure that the join is working

Is this even close?

--vicki