Working w/ Multiple Tables in MySQL

Using JOIN

----------

# this joins 2 table via "name" fields

#

# table 1

+----------+-----------+---------+------+------------+------------+

| name     | owner     | species | sex  | birth      | death      |

+----------+-----------+---------+------+------------+------------+

| Whistler | Gwen      | bird    | NULL | 1997-12-09 | NULL       |

| Gunner   | Mark      | dog     | m    | 1992-01-14 | NULL       |

| Heath    | Stephanie | penguin | m    | 2001-12-12 | NULL       |

| Winnona  | Stephanie | dog     | m    | 2009-03-18 | NULL       |

| Hunter   | Stephanie | fish    | f    | 1997-09-21 | NULL       |

| Splitter | Gwen      | fish    | m    | 1996-01-12 | 1999-03-04 |

| Styder   | Gwen      | fish    | f    | 2018-05-17 | NULL       |

+----------+-----------+---------+------+------------+------------+

# table 2

+----------+------------+------------+------------+

| name     | date       | type       | remark     |

+----------+------------+------------+------------+

| Whistler | 0000-00-00 | litter     | arghhh     |

| Gunner   | 2001-01-13 | litter     | not again  |

| Heath    | 1991-09-24 | broken rib | huhu       |

| Winnona  | 2003-09-09 | pregnant   | yeyy       |

| Hunter   | 2011-12-12 | sick       | waaa       |

| Splitter | 1993-01-01 | sick       | waaa       |

| Styder   | 1994-05-06 | pregnant   | again yeyy |

+----------+------------+------------+------------+

# query

mysql>

mysql> SELECT pet.name,

    -> TIMESTAMPDIFF(YEAR,birth,date) AS age,

    -> remark

    -> FROM pet INNER JOIN event

    ->   ON pet.name = event.name

    -> WHERE event.type = 'litter';

Using UNION

-----------

This is alternative to using OR:

mysql> select * from pet where owner='Gwen' union select * from pet where owner='Stephanie';

+----------+-----------+---------+------+------------+------------+

| name     | owner     | species | sex  | birth      | death      |

+----------+-----------+---------+------+------------+------------+

| Whistler | Gwen      | bird    | NULL | 1997-12-09 | NULL       |

| Splitter | Gwen      | fish    | m    | 1996-01-12 | 1999-03-04 |

| Styder   | Gwen      | fish    | f    | 2018-05-17 | NULL       |

| Heath    | Stephanie | penguin | m    | 2001-12-12 | NULL       |

| Winnona  | Stephanie | dog     | m    | 2009-03-18 | NULL       |

| Hunter   | Stephanie | fish    | f    | 1997-09-21 | NULL       |

+----------+-----------+---------+------+------------+------------+

6 rows in set (0.00 sec)

mysql>