[ale] MySQL help

[Jim Kinney]

Excellent answer.

+1!
On Mar 13, 2015 4:57 PM, "Alex Carver" <agcarver+ale at acarver.net> wrote:

> 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?
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ale.org/pipermail/ale/attachments/20150313/3a7b65f3/attachment.html>