UkZub8G3

1-- Database: `timelapse`
2
3--
4-- Table structure for table `people`
5--
6
7CREATE TABLE IF NOT EXISTS `people` (
8  `id` int(11) NOT NULL auto_increment,
9  `name` varchar(255) NOT NULL,
10  PRIMARY KEY  (`id`)
11) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
12
13--
14-- Dumping data for table `people`
15--
16
17INSERT INTO `people` (`id`, `name`) VALUES
18(1, 'Luke'),
19(2, 'Tony');
20
21-- --------------------------------------------------------
22
23--
24-- Table structure for table `statuses`
25--
26
27CREATE TABLE IF NOT EXISTS `statuses` (
28  `id` int(11) NOT NULL auto_increment,
29  `person_id` int(11) NOT NULL,
30  `name` varchar(255) NOT NULL,
31  `assigned_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
32  PRIMARY KEY  (`id`)
33) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
34
35--
36-- Dumping data for table `statuses`
37--
38
39INSERT INTO `statuses` (`id`, `person_id`, `name`, `assigned_at`) VALUES
40(1, 1, 'Wibble', '2009-02-04 12:53:35'),
41(2, 1, 'Wobble', '2009-02-04 12:53:49'),
42(3, 2, 'FooBar', '2009-02-04 12:53:59');
43
44--
45-- Example Query
46--
47
48SELECT people.id, people.name, (
49    SELECT statuses.name
50    FROM statuses
51    WHERE people.id = statuses.person_id AND statuses.assigned_at <= NOW()
52    ORDER BY statuses.assigned_at DESC
53    LIMIT 1) as current_status
54FROM people