[ale] MySQL help

Alex Carver agcarver+ale at acarver.net
Fri Mar 13 17:55:25 EDT 2015


There are four JOINs available to you, LEFT, RIGHT, INNER (this one is
usually just the unadorned JOIN keyword) and OUTER.

To see if you have an animal with no food and given that your food table
is on the right of the animal table in the query you offered you need to
use a LEFT JOIN.  The LEFT JOIN ensures that the left table takes
precedence during row generation such that missing matches result in
NULLs in the fields from the right-side tables:

So change your query to:

SELECT id, name, family, feed FROM animal LEFT JOIN food ON animal.id =
food.animalid;

That gives you everything.  Then add a WHERE clause to filter out
animals missing food:

SELECT id, name, family, feed FROM animal LEFT JOIN food ON animal.id =
food.animalid WHERE feed IS NULL;

The unadorned JOIN (which is a INNER or STRAIGHT JOIN) will only show
records where there are matches in all joined tables.  The RIGHT JOIN
variant will reverse the precedence of the tables (LEFT and RIGHT JOINs
work the same, the precedence is reversed and it's mainly used when you
join many tables so that you get the precedence correct for the needed
query).

On an aside, the table as constructed isn't quite proper according to
the rules of Normal Form (how databases are laid out).  Your food table
should not have an animal ID.  Instead your animal should have a food ID
if it can only eat one type of food.  Alternatively there should be a
third table which maps animal ID to food ID that way an animal can eat
more than one food.

Two table variant:
Animal: {id, name, family, weight, foodid}
Food: {id, feed}

Three table variant:
Animal: {id, name, family, weight}
Food: {id, feed}
AnimalFoodMap: {animalid, foodid}

In the three table variant you join the animal and food tables using the
AnimalFoodMap as "glue":

SELECT name, family, weight, feed FROM Animal LEFT JOIN AnimalFoodMap ON
animal.id = AnimalFoodMap.animalid LEFT JOIN Food ON
AnimalFoodMap.foodid = Food.id;

If an animal has more than one food type, the animal is listed once in
the Animal table, the foods are each listed only once in the Food table
and the AnimalFoodMap table contains the appropriate pairings.  Your
example has more than one food per animal and also one repeated food so
let's modify it:

Animal (table):
id  name    family   weight
1  Seymore  Snake    10
2  Gerard   Giraffe   120

Food (table):
id  feed
1   mice
2   leaves
3   grass

Now the magic AnimalMapTable:
animalid   foodid
1          1
1          2
2          2
2          3

(Note that I'm skipping over some things such as keys that should be
used to ensure there are no duplicates)

After doing this there are several additional ways to write up queries
to make an output similar to what you have (single line for an animal
plus all the entries for the foods) which requires some more complicated
query structure.  But for now, the query I wrote above would output:

id name      family  feed
1  Seymore   Snake   mice
1  Seymore   Snake   leaves
2  Gerard    Giraffe leaves
2  Gerard    Giraffe grass


On 2015-03-13 10:52, David S Jackson wrote:
> Hi,
> 
> I'm a MySQL newbie.  I'm just starting to use Python to talk to MySQL
> databases, but first I need to understand the MySQL query language!
> 
> 
> So, I have two tables:  (zoo) animal and food.
> 
> animal has the columns ID, NAME, FAMILY, WEIGHT
> 
> food has  ID, ANIMALID, FEED
> 
> 
> So if I go: select id, name, family, feed from animal JOIN food ON
> animal.id=food.animalid;
> 
> 
> I get something like:
> 
> ID   NAME        FAMILY      FEED
> 1    Seymore   Snake        mice, leaves
> 2    Gerard      Giraffe       leaves, grass
> ...
> etc
> 
> So, if I have a situation where I want to see whether an animal has been
> added the animal table but may have not been added to the food table,
> how would I compose that query?
> 
> That is, the animal could have been added to the animal table and could
> get an id, say 10, but the food table could have 20 foods already
> entered and the animal-id would be used several times.
> 
> I was thinking, is there a way I can ask, "does the highest animal.id
> number equal the highest food.animalid number"?  If not, what animal is
> not getting fed?



More information about the Ale mailing list