Re: Stupid Query-thinking?

From: Jan Pieter Kunst (devnull_at_cauce.org)
Date: 01/05/05 

Date: Wed, 05 Jan 2005 21:35:22 +0100

knoak wrote:
> Hi there,
>
> I'm building a website about animals.
> There is a mySQL DB behind it.
> There are 2 tables: 1 - species
> 2 - animals
>
> Species could be: birds, fishes etc.
> Animals would be: hawk, eagle etc, and orca, whale etc.
> So every animal belongs to one of the species.
>
> Anyway, there is an admin part to delete or edit animals or entire
> species.
>
> You get an overview of all the species, and behin it is a number of
> how much animals there are in it.

assuming the following tables in the db:

mysql> select * from species;
+------------+---------+
| species_id | species |
+------------+---------+
| 1 | birds |
| 2 | fishes |
+------------+---------+

mysql> select * from animal;
+-----------+--------+------------+
| animal_id | animal | species_id |
+-----------+--------+------------+
| 1 | hawk | 1 |
| 2 | eagle | 1 |
| 3 | orca | 2 |
| 4 | whale | 2 |
+-----------+--------+------------+

You can get your numbers in one pass with this query:

mysql> select species, count(animal.species_id) as 'number of animals'
from species, animal where species.species_id=animal.species_id group by
species;
+---------+-------------------+
| species | number of animals |
+---------+-------------------+
| birds | 2 |
| fishes | 2 |
+---------+-------------------+

HTH,
JP 

-- 
Sorry, <devnull@cauce.org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

Relevant Pages

  • Re: Aggression, human nature and paleopsychology
    ... Instincts are not good or bad in themselves but more or less ... Human beings share with other animals instinctual inhibitions against ... killing members of our own species. ... > human aggression also raises some basic and profound questions ...
    (sci.bio.evolution)
  • Zoo Torah: Correction, and The Religion of Conservation Part II
    ... The Religion of Conservation, Part II ... "Even the least of creatures should be extremely important in his eyes ... species is surviving, not if any given individual is surviving. ... though it did allow the slaughter of animals of that species. ...
    (soc.culture.jewish.moderated)
  • Re: Zoo Torah: The Religion of Conservation, part one
    ... >>> it was a protected species. ... many people take conservation very seriously. ... And does Judaism have anything to say ... Animals and plants can be of tremendous value to mankind in many ...
    (soc.culture.jewish.moderated)
  • Interview with E.O. Wilson
    ... Konrad Lorenz wrote that animals of the same species sometimes by ... mortali­ty due to direct aggression during war in the modern area. ... There are about 9.500 known species of ants, many of whom you studied, ...
    (sci.bio.evolution)
  • Re: Evolution and Love
    ... >> dynamics in a particular species, based on their habitat, diet etc. ... > invest a lot of resources in each, while r selected animals have many ... > According to the parental investment theory, ... >> Monogamy is not a universal trait though, it exists in species where it ...
    (sci.bio.evolution)