[ale] MySQL help

[Charles Shapiro]

Heh. Hadda look that one up.   Could work great, depending on resources and
purpose.

-- CHS


On Fri, Mar 13, 2015 at 2:51 PM, DJ-Pfulio <DJPfulio at jdpfu.com> wrote:

> 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
> >
> _______________________________________________
> 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/51722e5d/attachment.html>