MySQL Forums
Forum List  »  Newbie

Question about multi-table query (from tutorial)
Posted by: Marc M
Date: July 07, 2005 02:16AM

Going through the tutorials there's something that I just don't quite get -

In the section 3.3.4.9. using more than one table...

---------------------------------------------
Suppose that you want to find out the ages at which each pet had its litters. We saw earlier how to calculate ages from two dates. The litter date of the mother is in the event table, but to calculate her age on that date you need her birth date, which is stored in the pet table. This means the query requires both tables:

mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = 'litter';
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
----------------------------------------------------------------------

It says that we need to calculate the age of the mother on here litter date. In order to do this we need the birth date from the "pet" table and the litter date from the "event" table. That part I understand. But I don't see this in the command above. It seems as if the age is being calulated on the date information from the pet table? So how are we getting the litter date into this calculation?

I'm sure it's right there in front of me, but yet it's not - quite - there - just - - - yet.

The way I see this so far is:
select the name column from the table "pet",
(calculate the age based upon year-birth - 1or0)
as age, remark (not exactly sure here, but I guess we're selecting the age and remark columns)
from the pet and event tables
where the names match and the type of even is litter.

But the litter date is separate from the current date, which seems to be used above. ???

Go easy on me - I'm a nubian. ;)

------------------------
Inner space - what a trip.

Options: ReplyQuote


Subject
Written By
Posted
Question about multi-table query (from tutorial)
July 07, 2005 02:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.