[ale] MySQL help

[Charles Shapiro]

Hmm. I'm not that familiar with MySQL, but in PostgreSQL the way to
determine what you want is

select
   animal.id
from
   animal
left join
   food
on
   animal.id = food.animalid
where
   food.id is null
;


Writing SQL queries takes some practice.  One handy tip I've found is to
write the "from..." part first, then go back and fill in what columns you
wish to select.

Doing anything based on the numeric value of the "id" field is a Bad Idea.
In most relational databases, that field just increments when you add a
record to your table.  It never decrements when you delete records.  That
means that the  ID value itself is useless in determining what is or is not
in a table.   It's only useful when it's in another table -- hence the
name, "Relational".


Your current table setup also breaks down if you add -- say -- a goat which
wants to eat the same things as the giraffe. You're more likely to have
several animals linked to the same foods than several foods linked to the
same animal.  Don't you really want a "FoodFk" in the "animal" table?  Then
you could have lots of animals which eat the same thing.  Of course, I
reckon in reality you'd need a jump table to link foods and animals:

food_animal { integer animalid references animal(id), integer foodid
references food(id) }

Then you could have lots of different foods and lots of different animals
all linked together.


-- CHS


On Fri, Mar 13, 2015 at 1:52 PM, David S Jackson <deepbsd.ale at gmail.com>
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?
>
> Dave
>
>
>
>
> _______________________________________________
> 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/00dafbe5/attachment.html>