StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Query to integrate information from 3 tables (with plenty of obstacles)
text
Body
copied!<p>Background: In an experiment bees are glued number tags on their backs which and their choices in a lab are recorded. Not having enough number tags (2 digits and a few color options) they need to be reused. However, a tag is only reused after the one carrying it dies. Therefore, in the data structure we occasionally see bee identifiers but the only way to know whether it's from the same bee or not is by looking in another table to see whether the bee died or not. </p> <p>The Tables: The choices bees make</p> <pre><code>CREATE TABLE `exp8` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `bee_id` varchar(255) DEFAULT NULL, `date_time` datetime DEFAULT NULL, `choice` varchar(255) DEFAULT NULL, `hover_duration` int(11) DEFAULT NULL, `antennate_duration` int(11) DEFAULT NULL, `land_duration` int(11) DEFAULT NULL, `landing_position` varchar(255) DEFAULT NULL, `remarks` longtext, `validity` int(11) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=264; LOCK TABLES `exp8` WRITE; /*!40000 ALTER TABLE `exp8` DISABLE KEYS */; INSERT INTO `exp8` (`id`, `bee_id`, `date_time`, `choice`, `hover_duration`, `antennate_duration`, `land_duration`, `landing_position`, `remarks`, `validity`) VALUES (1,NULL,'2013-05-14 15:38:31','right',1,0,0,NULL,NULL,1), (2,NULL,'2013-05-18 10:27:15','left',1,0,0,NULL,NULL,1), (3,'G5','2013-05-18 11:44:44','left',0,0,4,'yellow',NULL,1), (4,'G5','2013-06-01 10:00:00','left',0,0,4,'yellow',NULL,1); </code></pre> <p>The time of birth and death tags</p> <pre><code>CREATE TABLE `tags` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `bee_id` varchar(255) DEFAULT NULL, `tag_date` date DEFAULT NULL, `colony_id` int(11) DEFAULT NULL, `events` varchar(255) DEFAULT NULL, `worker_age` varchar(255) DEFAULT NULL, `tagged_by` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB AUTO_INCREMENT=406; LOCK TABLES `tags` WRITE; /*!40000 ALTER TABLE `tags` DISABLE KEYS */; INSERT INTO `tags` (`id`, `bee_id`, `tag_date`, `colony_id`, `events`, `worker_age`, `tagged_by`) VALUES (1,'G5','2013-05-08',1,'birth','Adult','ET'), (2,'G5','2013-05-20',NULL,'death','Adult','ET'), (3,'G5','2013-05-29',1,'birth','Adult','ET'); </code></pre> <p>And the stimuli that are being displayed in the lab</p> <pre><code>CREATE TABLE `stimuli_schedule` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `left_side` varchar(255) DEFAULT NULL, `right_side` varchar(255) DEFAULT NULL, `start_datetime` datetime DEFAULT NULL, `scheduled` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB AUTO_INCREMENT=50; LOCK TABLES `stimuli_schedule` WRITE; /*!40000 ALTER TABLE `stimuli_schedule` DISABLE KEYS */; INSERT INTO `stimuli_schedule` (`id`, `left_side`, `right_side`, `start_datetime`, `scheduled`) VALUES (1,'LS1','LS2','2013-05-14 12:00:00',NULL), (2,'LS2','LS1','2013-05-15 11:44:00',NULL), (3,'LS1','LS2','2013-05-30 11:09:00',NULL); </code></pre> <p>The desired output is something like this: </p> <pre><code>bee_id CHOICE_DATETIME LEFT_SIDE RIGHT_SIDE CHOICE =================================================================== NULL 2013-05-14 15:38:31 LS1 LS2 right G5 2013-05-18 10:27:15 LS2 LS1 left G5 2013-06-01 10:00:00 LS1 LS2 left </code></pre> <p>Thanks to the generous help of @GordonLinoff and @jcsanyi there are two related MySQL queries that achieve part of the solution: </p> <p>This bit shows each individual bee's choice, assuming that a bee's ID is unique: </p> <pre><code>select bee_id, count(case when choice="left" then 1 else NULL end) as leftCount, count(case when choice="right" then 1 else NULL end) as rightCount from exp8 e left join stimuli_schedule ss on ss.start_datetime <= e.date_time left join stimuli_schedule ss2 on ss2.start_datetime <= e.date_time where (bee_id IS NOT NULL) AND (ss2.left_side IN ('LA1','HS1') AND ss2.right_side IN('HS1','LA1')) group by bee_id </code></pre> <p>This bit is capable of showing a bees length of life, and distinguishes between reused tags: </p> <pre><code>select t.bee_id, (case when t.death_date is null then 'Alive' else 'Dead' end) as status, t.tag_date, t.death_date, (case when t.death_date is not null then timediff(t.death_date,t.tag_date) else timediff(NOW(),t.tag_date) end) as age from (select t.*, (select t2.tag_date from tags t2 where t2.bee_id = t.bee_id and t2.events = 'death' and t2.tag_date >= t.tag_date limit 1 ) as death_date from tags t where t.events = 'birth' ) t group by t.bee_id, t.tag_date; </code></pre> <p>I am having trouble combining the two queries to produce the desired output. Here is my attempt: </p> <pre><code>select t.bee_id, count(case when choice="left" then 1 else NULL end) as leftCount, count(case when choice="right" then 1 else NULL end) as rightCount, (case when t.death_date is null then 'Alive' else 'Dead' end) as status, t.tag_date, t.death_date, (case when t.death_date is not null then timediff(t.death_date,t.tag_date) else timediff(NOW(),t.tag_date) end) as "age (hours)" from exp8 e, (select t.*, (select t2.tag_date from tags t2 where t2.bee_id = t.bee_id and t2.events = 'death' and t2.tag_date >= t.tag_date limit 1 ) as death_date from tags t where t.events = 'birth' ) t left join stimuli_schedule ss on ss.start_datetime <= e.date_time left join stimuli_schedule ss2 on ss2.start_datetime <= e.date_time where (e.bee_id IS NOT NULL) group by t.bee_id, t.tag_date; </code></pre> <p>For reasons beyond my understanding, the left e.date_time portion is causing an "unknown column" error. </p> <p>Any help would be much appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload