[ale] MySQL help

[Pete Hardie]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ale.org/pipermail/ale/attachments/20150313/5dd4f04d/attachment.html>