[ale] MySQL help

[David Jackson]

Thanks Alex!  Wow.  Very impressive analysis!

This has been for an assignment, so I couldn't change the tables, but your
way of gluing the info together makes sense to me, so thanks!  I think
they're just trying to keep it simple for the class

It looks like for MySQL you want to "atomize" the data as much as possible
to keep the structure out of the way of possible relations among the
data??  I guess it's a judgment call to not get too "atomic" though, huh?

Dave


On Fri, Mar 13, 2015 at 5:55 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/20150314/b7c6469b/attachment.html>