[ale] MySQL help

David S Jackson deepbsd.ale at gmail.com
Fri Mar 13 15:53:29 EDT 2015


Thanks Charles!

I appreciate the good advice.  For background, this is for an OReilly 
School of Technology (ooooh) certificate in python programming.  It's 
two chapters on having your python scripts work with mysql databases.  
Trouble is, it's on their remote servers rather than on my home grown 
server, so I can't seem to get my local mysql server to behave.  So I'm 
testing on theirs through ssh. (I'll have more mysql admin questions in 
another thread!)

I tried this query you suggested with a change or two:

select * from animal left join food on animal.id=food.animalid where 
food.id=NULL;

I got an empty set.

when I went select * from animal left join food on animal.id=food.animalid;

at least I got everything from both tables and could see the NULL values 
on the food table.

I'm dumbfounded.  Seems to me the 'where food.id=NULL' should have 
eliminated everything except the hungry porcupine!  I'm going to have to 
ask my teacher for help on this one.   Your idea should have worked, 
near as I can figure!

If you get any ideas on why I got empty set, I'm all ears!

Thanks again!
Dave


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
>
>
>
>
>     _______________________________________________
>     Ale mailing list
>     Ale at ale.org <mailto:Ale at ale.org>
>     http://mail.ale.org/mailman/listinfo/ale
>     See JOBS, ANNOUNCE and SCHOOLS lists at
>     http://mail.ale.org/mailman/listinfo
>
>
>
>
> _______________________________________________
> 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/f4e88801/attachment.html>


More information about the Ale mailing list