[ale] MySQL help

Sat Mar 14 17:46:36 EDT 2015

Hey Pete,

Yeah, that appears to be how it is for me now.  food.anid=NULL gave an
empty set, but food.anid IS NULL gave me what I wanted.  If computers ever
start talking back like they did in Star Trek, I can imagine some
frustrating conversations!

Thanks!
Dave

On Fri, Mar 13, 2015 at 4:10 PM, Pete Hardie <pete.hardie at gmail.com> wrote:

> I'm by no means an SQL expert, but I have had dialects where you had to
> say 'columnName IS null' instead of 'columnName=null
>
> On Fri, Mar 13, 2015 at 3:53 PM, David S Jackson <deepbsd.ale at gmail.com>
> wrote:
>
>>  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
>>  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
>>>
>>
>>
>>
>> _______________________________________________
>> Ale mailing listAle at ale.orghttp://mail.ale.org/mailman/listinfo/ale
>> See JOBS, ANNOUNCE and SCHOOLS lists athttp://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
>>
>>
>
>
> --
> Pete Hardie
> --------
> Better Living Through Bitmaps
>
> _______________________________________________
> 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/c2e6eec9/attachment.html>