SQL Query advice needed.

  • hgun77
  • Graduate
  • Graduate
  • User avatar
  • Posts: 164
  • Loc: Malaysia

Post 3+ Months Ago

I have 2 tables.

1. tbl_people
==============
id
name
state (0=dead, 1=alive)

2.tbl_pet
==============
id
name
owner_id(people id)
born_datetime(once it born it will immedialy have a owner)
state (0=dead, 1=alive)


Query: To get latest pet(alive) for all people(alive)

My Query(MySQL):
1)
SELECT ppl.`name`, pet.`name`
FROM `tbl_people` ppl, `tbl_pet` pet
WHERE pet.`born_datetime` IN (
SELECT MAX(`born_datetime`)
FROM `tbl_pet`
GROUP BY `owner_id`
)
AND pet.`owner_id` = ppl.`id`
AND ppl.`state` = 1
AND pet.`state` = 1


This can get what I want, but i am worry that when the table go large, it will bcome very slow since i have sub select... is there any other way?


AND...

can i do this, it is better or worse?

2)
SELECT ppl.`name`, pet.`name`
FROM (
SELECT *
FROM `tbl_people`
WHERE `state` = 1
) ppl,
(
SELECT *
FROM `tbl_pet`
WHERE `state` = 1
) pet
WHERE pet.`born_datetime` IN (
SELECT MAX(`born_datetime`)
FROM `tbl_pet`
GROUP BY `owner_id`
)
AND pet.`owner_id` = ppl.`id`


Please advice or comments. Thanks a lot.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Is there an assumption that only the latest pet is alive? Also, can one person own more than one pet at a time? Can one pet belong to multiple people at a time?
  • hgun77
  • Graduate
  • Graduate
  • User avatar
  • Posts: 164
  • Loc: Malaysia

Post 3+ Months Ago

yeap, I am interest on alive people or pet only.
one people can who many pets at same time. one pet can only belong to one people.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

You could plop a newest_pet_id column on tbl_people linked to id in tbl_pets. Then update that column when a new pet is aquired/born. When their newest pet dies the column can be nulled out indicating no newest living pet.
  • hgun77
  • Graduate
  • Graduate
  • User avatar
  • Posts: 164
  • Loc: Malaysia

Post 3+ Months Ago

thx brother, will use ur approach
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

This is almost guaranteed to have some kind of error (it's 8am and I haven't had my caffeine yet!), but it's heading down the right path. It should return the latest pet from each owner's group of pets.

Code: [ Select ]
SELECT pets.name,ppl.name,MAX(pets.born_datetime) AS Born FROM tbl_pet pets
LEFT JOIN tbl_people ppl on ppl.id = pets.owner_id
WHERE pets.state = '1'
GROUP BY ppl.name
  1. SELECT pets.name,ppl.name,MAX(pets.born_datetime) AS Born FROM tbl_pet pets
  2. LEFT JOIN tbl_people ppl on ppl.id = pets.owner_id
  3. WHERE pets.state = '1'
  4. GROUP BY ppl.name
  • hgun77
  • Graduate
  • Graduate
  • User avatar
  • Posts: 164
  • Loc: Malaysia

Post 3+ Months Ago

@Caffeine

Thanks you, I got what your query means.

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 139 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.