<div dir="ltr"><div><div><div>Thanks Alex! Wow. Very impressive analysis!<br><br></div>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<br><br></div>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?<br><br></div>Dave<br><div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Mar 13, 2015 at 5:55 PM, Alex Carver <span dir="ltr"><<a href="mailto:agcarver+ale@acarver.net" target="_blank">agcarver+ale@acarver.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">There are four JOINs available to you, LEFT, RIGHT, INNER (this one is<br>
usually just the unadorned JOIN keyword) and OUTER.<br>
<br>
To see if you have an animal with no food and given that your food table<br>
is on the right of the animal table in the query you offered you need to<br>
use a LEFT JOIN. The LEFT JOIN ensures that the left table takes<br>
precedence during row generation such that missing matches result in<br>
NULLs in the fields from the right-side tables:<br>
<br>
So change your query to:<br>
<br>
SELECT id, name, family, feed FROM animal LEFT JOIN food ON <a href="http://animal.id" target="_blank">animal.id</a> =<br>
food.animalid;<br>
<br>
That gives you everything. Then add a WHERE clause to filter out<br>
animals missing food:<br>
<br>
SELECT id, name, family, feed FROM animal LEFT JOIN food ON <a href="http://animal.id" target="_blank">animal.id</a> =<br>
food.animalid WHERE feed IS NULL;<br>
<br>
The unadorned JOIN (which is a INNER or STRAIGHT JOIN) will only show<br>
records where there are matches in all joined tables. The RIGHT JOIN<br>
variant will reverse the precedence of the tables (LEFT and RIGHT JOINs<br>
work the same, the precedence is reversed and it's mainly used when you<br>
join many tables so that you get the precedence correct for the needed<br>
query).<br>
<br>
On an aside, the table as constructed isn't quite proper according to<br>
the rules of Normal Form (how databases are laid out). Your food table<br>
should not have an animal ID. Instead your animal should have a food ID<br>
if it can only eat one type of food. Alternatively there should be a<br>
third table which maps animal ID to food ID that way an animal can eat<br>
more than one food.<br>
<br>
Two table variant:<br>
Animal: {id, name, family, weight, foodid}<br>
Food: {id, feed}<br>
<br>
Three table variant:<br>
Animal: {id, name, family, weight}<br>
Food: {id, feed}<br>
AnimalFoodMap: {animalid, foodid}<br>
<br>
In the three table variant you join the animal and food tables using the<br>
AnimalFoodMap as "glue":<br>
<br>
SELECT name, family, weight, feed FROM Animal LEFT JOIN AnimalFoodMap ON<br>
<a href="http://animal.id" target="_blank">animal.id</a> = AnimalFoodMap.animalid LEFT JOIN Food ON<br>
AnimalFoodMap.foodid = Food.id;<br>
<br>
If an animal has more than one food type, the animal is listed once in<br>
the Animal table, the foods are each listed only once in the Food table<br>
and the AnimalFoodMap table contains the appropriate pairings. Your<br>
example has more than one food per animal and also one repeated food so<br>
let's modify it:<br>
<br>
Animal (table):<br>
id name family weight<br>
1 Seymore Snake 10<br>
2 Gerard Giraffe 120<br>
<br>
Food (table):<br>
id feed<br>
1 mice<br>
2 leaves<br>
3 grass<br>
<br>
Now the magic AnimalMapTable:<br>
animalid foodid<br>
1 1<br>
1 2<br>
2 2<br>
2 3<br>
<br>
(Note that I'm skipping over some things such as keys that should be<br>
used to ensure there are no duplicates)<br>
<br>
After doing this there are several additional ways to write up queries<br>
to make an output similar to what you have (single line for an animal<br>
plus all the entries for the foods) which requires some more complicated<br>
query structure. But for now, the query I wrote above would output:<br>
<br>
id name family feed<br>
1 Seymore Snake mice<br>
1 Seymore Snake leaves<br>
2 Gerard Giraffe leaves<br>
2 Gerard Giraffe grass<br>
<div class="HOEnZb"><div class="h5"><br>
<br>
On 2015-03-13 10:52, David S Jackson wrote:<br>
> Hi,<br>
><br>
> I'm a MySQL newbie. I'm just starting to use Python to talk to MySQL<br>
> databases, but first I need to understand the MySQL query language!<br>
><br>
><br>
> So, I have two tables: (zoo) animal and food.<br>
><br>
> animal has the columns ID, NAME, FAMILY, WEIGHT<br>
><br>
> food has ID, ANIMALID, FEED<br>
><br>
><br>
> So if I go: select id, name, family, feed from animal JOIN food ON<br>
> <a href="http://animal.id" target="_blank">animal.id</a>=food.animalid;<br>
><br>
><br>
> I get something like:<br>
><br>
> ID NAME FAMILY FEED<br>
> 1 Seymore Snake mice, leaves<br>
> 2 Gerard Giraffe leaves, grass<br>
> ...<br>
> etc<br>
><br>
> So, if I have a situation where I want to see whether an animal has been<br>
> added the animal table but may have not been added to the food table,<br>
> how would I compose that query?<br>
><br>
> That is, the animal could have been added to the animal table and could<br>
> get an id, say 10, but the food table could have 20 foods already<br>
> entered and the animal-id would be used several times.<br>
><br>
> I was thinking, is there a way I can ask, "does the highest <a href="http://animal.id" target="_blank">animal.id</a><br>
> number equal the highest food.animalid number"? If not, what animal is<br>
> not getting fed?<br>
<br>
</div></div><div class="HOEnZb"><div class="h5">_______________________________________________<br>
Ale mailing list<br>
<a href="mailto:Ale@ale.org">Ale@ale.org</a><br>
<a href="http://mail.ale.org/mailman/listinfo/ale" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a><br>
See JOBS, ANNOUNCE and SCHOOLS lists at<br>
<a href="http://mail.ale.org/mailman/listinfo" target="_blank">http://mail.ale.org/mailman/listinfo</a><br>
</div></div></blockquote></div><br></div>