[ale] MySQL help

[DJ-Pfulio]

Or just use an ORM.

On 03/13/2015 02:36 PM, Charles Shapiro wrote:
> Hmm. I'm not that familiar with MySQL, but in PostgreSQL the way to determine
> what you want is
> 
> select
>    animal.id <http://animal.id>
> from
>    animal
> left join
>    food
> on
>    animal.id <http://animal.id> = food.animalid
> where
>    food.id <http://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
> <mailto: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
>     <http://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
>     <http://animal.id> number equal the highest food.animalid number"?  If not,
>     what animal is not getting fed?
> 
>     Dave
>