gistfile1.sql

-- Database: `timelapse`

--
-- Table structure for table `people`
--

CREATE TABLE IF NOT EXISTS `people` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `people`
--

INSERT INTO `people` (`id`, `name`) VALUES
(1, 'Luke'),
(2, 'Tony');

-- --------------------------------------------------------

--
-- Table structure for table `statuses`
--

CREATE TABLE IF NOT EXISTS `statuses` (
  `id` int(11) NOT NULL auto_increment,
  `person_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `assigned_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `statuses`
--

INSERT INTO `statuses` (`id`, `person_id`, `name`, `assigned_at`) VALUES
(1, 1, 'Wibble', '2009-02-04 12:53:35'),
(2, 1, 'Wobble', '2009-02-04 12:53:49'),
(3, 2, 'FooBar', '2009-02-04 12:53:59');

--
-- Example Query
--

SELECT people.id, people.name, (
    SELECT statuses.name
    FROM statuses
    WHERE people.id = statuses.person_id AND statuses.assigned_at <= NOW()
    ORDER BY statuses.assigned_at DESC
    LIMIT 1) as current_status
FROM people