<div dir="ltr"><div><div>Hey Pete,<br><br></div>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!<br><br></div><div>Thanks!<br></div>Dave<br><div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Mar 13, 2015 at 4:10 PM, Pete Hardie <span dir="ltr"><<a href="mailto:pete.hardie@gmail.com" target="_blank">pete.hardie@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div style="font-family:arial,helvetica,sans-serif;font-size:small">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<br></div></div><div class="gmail_extra"><div><div class="h5"><br><div class="gmail_quote">On Fri, Mar 13, 2015 at 3:53 PM, David S Jackson <span dir="ltr"><<a href="mailto:deepbsd.ale@gmail.com" target="_blank">deepbsd.ale@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
Thanks Charles!<br>
<br>
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!)<br>
<br>
I tried this query you suggested with a change or two:<br>
<br>
select * from animal left join food on <a href="http://animal.id" target="_blank">animal.id</a>=food.animalid where
<a href="http://food.id" target="_blank">food.id</a>=NULL;<br>
<br>
I got an empty set.<br>
<br>
when I went select * from animal left join food on
<a href="http://animal.id" target="_blank">animal.id</a>=food.animalid; <br>
<br>
at least I got everything from both tables and could see the NULL
values on the food table.<br>
<br>
I'm dumbfounded. Seems to me the 'where <a href="http://food.id" target="_blank">food.id</a>=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!<br>
<br>
If you get any ideas on why I got empty set, I'm all ears!<br>
<br>
Thanks again!<br>
Dave<br>
<br>
<br>
<div>On 03/13/2015 02:36 PM, Charles Shapiro
wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>Hmm. I'm not that familiar with
MySQL, but in PostgreSQL the way to
determine what you want is<br>
<br>
</div>
select<br>
</div>
<div> <a href="http://animal.id" target="_blank">animal.id</a><br>
</div>
from<br>
</div>
animal<br>
</div>
left join<br>
</div>
food<br>
</div>
on<br>
</div>
<a href="http://animal.id" target="_blank">animal.id</a>
= food.animalid <br>
</div>
where<br>
</div>
<a href="http://food.id" target="_blank">food.id</a>
is null<br>
;<br>
<br>
<br>
</div>
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.<br>
<br>
</div>
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".<br>
<br>
<br>
</div>
<div>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:<br>
<br>
</div>
<div>food_animal { integer animalid references animal(id),
integer foodid references food(id) }<br>
<br>
</div>
<div>Then you could have lots of different foods and lots of
different animals all linked together.<br>
</div>
<div><br>
<br>
</div>
-- CHS<br>
<br>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Fri, Mar 13, 2015 at 1:52 PM, David
S Jackson <span dir="ltr"><<a href="mailto:deepbsd.ale@gmail.com" target="_blank">deepbsd.ale@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
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!<br>
<br>
<br>
So, I have two tables: (zoo) animal and food.<br>
<br>
animal has the columns ID, NAME, FAMILY, WEIGHT<br>
<br>
food has ID, ANIMALID, FEED<br>
<br>
<br>
So if I go: select id, name, family, feed from animal JOIN
food ON <a href="http://animal.id" target="_blank">animal.id</a>=food.animalid;<br>
<br>
<br>
I get something like:<br>
<br>
ID NAME FAMILY FEED<br>
1 Seymore Snake mice, leaves<br>
2 Gerard Giraffe leaves, grass<br>
...<br>
etc<br>
<br>
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?<br>
<br>
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.<br>
<br>
I was thinking, is there a way I can ask, "does the highest
<a href="http://animal.id" target="_blank">animal.id</a> number equal the highest
food.animalid number"? If not, what animal is not getting
fed?<br>
<br>
Dave<br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
Ale mailing list<br>
<a href="mailto:Ale@ale.org" target="_blank">Ale@ale.org</a><br>
<a href="http://mail.ale.org/mailman/listinfo/ale" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a><br>
See JOBS, ANNOUNCE and SCHOOLS lists at<br>
<a href="http://mail.ale.org/mailman/listinfo" target="_blank">http://mail.ale.org/mailman/listinfo</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Ale mailing list
<a href="mailto:Ale@ale.org" target="_blank">Ale@ale.org</a>
<a href="http://mail.ale.org/mailman/listinfo/ale" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a>
See JOBS, ANNOUNCE and SCHOOLS lists at
<a href="http://mail.ale.org/mailman/listinfo" target="_blank">http://mail.ale.org/mailman/listinfo</a>
</pre>
</blockquote>
<br>
</div>
<br>_______________________________________________<br>
Ale mailing list<br>
<a href="mailto:Ale@ale.org" target="_blank">Ale@ale.org</a><br>
<a href="http://mail.ale.org/mailman/listinfo/ale" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a><br>
See JOBS, ANNOUNCE and SCHOOLS lists at<br>
<a href="http://mail.ale.org/mailman/listinfo" target="_blank">http://mail.ale.org/mailman/listinfo</a><br>
<br></blockquote></div><br><br clear="all"><br></div></div><span class="HOEnZb"><font color="#888888">-- <br><div>Pete Hardie<br>--------<br>Better Living Through Bitmaps</div>
</font></span></div>
<br>_______________________________________________<br>
Ale mailing list<br>
<a href="mailto:Ale@ale.org">Ale@ale.org</a><br>
<a href="http://mail.ale.org/mailman/listinfo/ale" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a><br>
See JOBS, ANNOUNCE and SCHOOLS lists at<br>
<a href="http://mail.ale.org/mailman/listinfo" target="_blank">http://mail.ale.org/mailman/listinfo</a><br>
<br></blockquote></div><br></div></div></div>