Solved

SQL Challenge

Posted on 2011-02-11
23
331 Views
Last Modified: 2012-05-11
I have a (i think) simple SQL problem, but either the coffee hasn't kicked in, or it is more challenging than I think.

I will attach the tables so you can see it clearly.

I have two tables, on table with emp_id, qualification_id.

AND

another table with position_id, and qualification_id.

I want to return ONLY the emp_id's that have ALL of the matching qualification_id's in table one.

I can get ALL of them to return, but I am seemingly unable to remember how to just return those records that have ALL of the qualifications.


INSERT INTO tbl_emp_qualifications (record_id, emp_id, qualification_id, qual_from, qual_til, updated_by, updated_at) VALUES
  (171, 160, 43, '2010-04-10 01:38:00', '2011-04-24 21:30:00', 'jcadmin', '2010-04-10 01:38:02'),
  (173, 160, 46, '2010-04-10 01:38:00', '2011-05-24 21:30:00', 'jcadmin', '2010-04-10 01:38:21'),
  (191, 62, 46, '2010-04-15 10:40:00', '2010-08-12 20:30:00', 'jcadmin', '2010-04-15 10:40:34'),
  (193, 62, 43, '2010-04-15 10:41:00', '2010-07-13 20:30:00', 'jcadmin', '2010-04-15 10:41:09'),
  (196, 31, 43, '2010-04-22 14:12:00', '2011-04-20 23:34:00', 'jcadmin', '2010-04-22 14:12:25'),
  (204, 157, 43, '2010-04-22 21:27:00', '2010-07-21 21:27:00', 'jcadmin', '2010-04-22 21:27:13'),
  (205, 139, 43, '2010-04-22 21:28:00', '2010-07-21 21:28:00', 'jcadmin', '2010-04-22 21:27:43'),
  (210, 56, 43, '2010-04-24 21:45:00', '2011-04-15 01:09:00', 'jcadmin', '2010-04-24 21:45:09'),
  (211, 56, 46, '2010-04-24 21:45:00', '2011-05-15 01:09:00', 'jcadmin', '2010-04-24 21:45:20'),
  (214, 166, 43, '2010-04-28 09:29:00', '2010-07-27 20:30:00', 'jcadmin', '2010-04-28 09:29:13'),
  (220, 31, 46, '2010-04-30 23:48:00', '2011-04-20 23:34:00', 'jcadmin', '2010-04-30 23:48:22'),
  (229, 1, 43, '2010-05-05 00:50:00', '2011-04-20 23:37:00', 'jcadmin', '2010-05-05 00:50:34'),
  (242, 52, 43, '2010-05-18 21:50:00', '2010-08-16 22:42:00', 'jcadmin', '2010-05-18 21:49:48'),
  (243, 52, 46, '2010-05-18 21:50:00', '2010-09-15 22:42:00', 'jcadmin', '2010-05-18 21:50:08'),
  (257, 148, 43, '2010-06-02 14:53:00', '2010-08-31 14:53:00', 'jcadmin', '2010-06-02 14:52:51'),
  (259, 130, 43, '2010-06-02 14:54:00', '2010-09-01 12:01:00', 'jcadmin', '2010-06-02 14:53:45'),
  (266, 129, 43, '2010-06-07 09:31:00', '2011-04-20 23:38:00', 'jcadmin', '2010-06-07 09:31:32'),
  (267, 130, 54, '2010-06-07 09:53:00', '2010-09-05 09:53:00', 'jcadmin', '2010-06-07 09:53:13'),
  (273, 99, 43, '2010-06-11 09:49:00', '2010-11-04 14:23:00', 'jcadmin', '2010-06-11 09:49:20'),
  (275, 99, 45, '2010-06-11 09:49:00', '2010-09-19 10:23:00', 'jcadmin', '2010-06-11 09:49:52'),
  (279, 88, 45, '2010-06-11 09:58:00', '2010-12-15 16:24:00', 'jcadmin', '2010-06-11 09:58:02'),
  (282, 16, 45, '2010-06-14 09:38:00', '2010-09-20 19:00:00', 'jcadmin', '2010-06-14 09:38:26'),
  (286, 129, 45, '2010-06-14 11:59:00', '2011-04-20 23:38:00', 'jcadmin', '2010-06-14 11:59:07'),
  (291, 10, 45, '2010-06-16 12:40:00', '2010-12-18 18:00:00', 'jcadmin', '2010-06-16 12:40:20'),
  (294, 15, 45, '2010-06-24 23:00:00', '2010-10-24 19:00:00', 'jcadmin', '2010-06-24 23:00:37'),
  (295, 17, 45, '2010-06-26 01:40:00', '2010-10-03 19:00:00', 'jcadmin', '2010-06-26 01:40:20'),
  (297, 14, 46, '2010-07-02 14:45:00', '2010-09-30 14:45:00', 'jcadmin', '2010-07-02 14:44:52'),
  (300, 144, 43, '2010-07-03 01:57:00', '2010-10-01 01:57:00', 'jcadmin', '2010-07-03 01:56:59'),
  (301, 14, 45, '2010-07-04 00:46:00', '2010-10-10 19:00:00', 'jcadmin', '2010-07-04 00:45:56'),
  (305, 163, 43, '2010-07-05 00:10:00', '2010-11-09 13:21:00', 'jcadmin', '2010-07-05 00:10:33'),
  (306, 163, 45, '2010-07-05 00:11:00', '2010-11-19 13:21:00', 'jcadmin', '2010-07-05 00:10:41'),
  (310, 15, 43, '2010-07-18 00:57:00', '2011-04-11 17:06:00', 'jcadmin', '2010-07-18 00:57:35'),
  (311, 45, 41, '2010-07-18 00:58:00', '2010-10-08 10:18:00', 'jcadmin', '2010-07-18 00:58:40'),
  (312, 46, 41, '2010-07-18 01:00:00', '2010-10-08 10:23:00', 'jcadmin', '2010-07-18 01:00:04'),
  (314, 2, 41, '2010-07-18 01:02:00', '2010-08-15 19:30:00', 'jcadmin', '2010-07-18 01:02:27'),
  (315, 44, 41, '2010-07-18 17:00:00', '2010-10-08 09:17:00', 'jcadmin', '2010-07-18 17:00:35'),
  (316, 114, 41, '2010-07-18 17:59:00', '2010-10-01 20:12:00', 'jcadmin', '2010-07-18 17:58:50'),
  (318, 12, 45, '2010-07-21 15:49:00', '2011-04-20 23:39:00', 'jcadmin', '2010-07-21 15:49:33'),
  (321, 156, 43, '2010-07-23 01:05:00', '2010-10-19 16:30:00', 'jcadmin', '2010-07-23 01:05:22'),
  (322, 156, 41, '2010-07-23 01:09:00', '2010-08-20 16:30:00', 'jcadmin', '2010-07-23 01:08:56'),
  (326, 128, 43, '2010-07-23 01:18:00', '2010-10-21 01:18:00', 'jcadmin', '2010-07-23 01:17:47'),
  (330, 128, 42, '2010-07-23 01:18:00', '2010-10-21 01:18:00', 'jcadmin', '2010-07-23 01:17:58'),
  (344, 3, 41, '2010-07-23 01:30:00', '2010-11-28 14:38:00', 'jcadmin', '2010-07-23 01:30:31'),
  (348, 9, 42, '2010-07-23 01:31:00', '2011-04-20 23:38:00', 'jcadmin', '2010-07-23 01:31:39'),
  (351, 137, 41, '2010-07-23 01:40:00', '2011-04-20 23:35:00', 'jcadmin', '2010-07-23 01:39:55'),
  (352, 143, 43, '2010-07-23 01:50:00', '2010-12-13 14:41:00', 'jcadmin', '2010-07-23 01:50:15'),
  (353, 143, 41, '2010-07-23 01:50:00', '2010-10-14 15:41:00', 'jcadmin', '2010-07-23 01:50:24'),
  (354, 110, 43, '2010-07-23 02:10:00', '2010-10-21 02:10:00', 'jcadmin', '2010-07-23 02:10:17'),
  (355, 28, 42, '2010-07-24 10:53:00', '2011-04-20 23:34:00', 'jcadmin', '2010-07-24 10:53:09'),
  (356, 14, 41, '2010-07-24 10:54:00', '2010-09-10 19:30:00', 'jcadmin', '2010-07-24 10:54:25'),
  (357, 71, 42, '2010-07-25 00:23:00', '2010-12-07 09:15:00', 'jcadmin', '2010-07-25 00:22:52'),
  (358, 71, 45, '2010-07-25 00:23:00', '2010-12-17 09:15:00', 'jcadmin', '2010-07-25 00:22:57'),
  (360, 18, 43, '2010-07-27 10:32:00', '2010-10-25 10:32:00', 'jcadmin', '2010-07-27 10:32:16'),
  (363, 20, 42, '2010-07-28 23:30:00', '2011-03-08 14:39:00', 'jcadmin', '2010-07-28 23:29:52'),
  (364, 123, 41, '2010-07-29 10:17:00', '2011-04-20 23:34:00', 'jcadmin', '2010-07-29 10:16:45'),
  (365, 16, 42, '2010-07-30 11:22:00', '2010-12-02 20:22:00', 'jcadmin', '2010-07-30 11:22:18'),
  (366, 103, 42, '2010-07-30 11:23:00', '2010-11-11 19:00:00', 'jcadmin', '2010-07-30 11:22:46'),
  (367, 95, 42, '2010-07-30 11:23:00', '2010-12-07 10:22:00', 'jcadmin', '2010-07-30 11:23:21'),
  (368, 166, 41, '2010-07-30 11:24:00', '2010-09-10 19:30:00', 'jcadmin', '2010-07-30 11:23:48'),
  (371, 129, 42, '2010-07-30 11:26:00', '2011-04-20 23:38:00', 'jcadmin', '2010-07-30 11:26:14'),
  (372, 161, 42, '2010-07-30 11:27:00', '2010-11-09 19:30:00', 'jcadmin', '2010-07-30 11:26:56'),
  (373, 38, 42, '2010-07-31 18:50:00', '2010-11-30 21:14:00', 'jcadmin', '2010-07-31 18:50:04'),
  (374, 88, 42, '2010-07-31 18:52:00', '2010-12-05 16:24:00', 'jcadmin', '2010-07-31 18:52:14'),
  (375, 383, 56, '2010-07-29 11:19:00', '2010-10-27 11:19:00', 'user_id347', '2010-07-29 11:19:26'),
  (376, 393, 56, '2010-07-29 11:19:00', '2010-10-27 11:19:00', 'user_id347', '2010-07-29 11:19:34'),
  (377, 386, 56, '2010-07-29 11:38:00', '2011-01-02 03:00:00', 'user_id347', '2010-07-29 11:37:49'),
  (378, 387, 56, '2010-07-29 11:38:00', '2011-01-02 03:00:00', 'user_id347', '2010-07-29 11:37:56'),
  (379, 356, 56, '2010-07-29 11:40:00', '2011-01-01 12:00:00', 'user_id347', '2010-07-29 11:40:34'),
  (380, 358, 56, '2010-07-29 11:47:00', '2011-01-01 22:00:00', 'user_id347', '2010-07-29 11:47:23'),
  (381, 420, 56, '2010-07-29 11:48:00', '2011-01-01 22:00:00', 'user_id347', '2010-07-29 11:47:40'),
  (382, 392, 56, '2010-07-29 12:51:00', '2011-01-02 06:00:00', 'user_id347', '2010-07-29 12:51:24'),
  (383, 400, 56, '2010-07-29 12:52:00', '2011-01-02 06:00:00', 'user_id347', '2010-07-29 12:51:44'),
  (384, 353, 56, '2010-07-29 12:52:00', '2010-11-29 13:56:00', 'user_id347', '2010-07-29 12:51:56'),
  (385, 375, 56, '2010-07-29 12:52:00', '2010-11-29 13:56:00', 'user_id347', '2010-07-29 12:52:13'),
  (386, 402, 56, '2010-07-29 12:52:00', '2011-01-02 08:00:00', 'user_id347', '2010-07-29 12:52:38'),
  (387, 398, 56, '2010-07-29 12:53:00', '2011-01-02 08:00:00', 'user_id347', '2010-07-29 12:53:04'),
  (388, 362, 56, '2010-07-29 12:55:00', '2011-01-01 16:00:00', 'user_id347', '2010-07-29 12:55:16'),
  (389, 371, 56, '2010-07-29 12:55:00', '2011-01-01 16:00:00', 'user_id347', '2010-07-29 12:55:27'),
  (390, 394, 56, '2010-07-29 12:56:00', '2011-01-02 06:00:00', 'user_id347', '2010-07-29 12:55:51'),
  (391, 395, 56, '2010-07-29 12:56:00', '2011-01-02 06:00:00', 'user_id347', '2010-07-29 12:56:02'),
  (392, 407, 56, '2010-07-29 12:56:00', '2010-10-27 12:56:00', 'user_id347', '2010-07-29 12:56:30'),
  (397, 400, 57, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:22:49'),
  (398, 400, 59, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:22:58'),
  (399, 400, 58, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:23:03'),
  (400, 406, 57, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:23:18'),
  (401, 406, 59, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:23:25'),
  (402, 406, 60, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:23:32'),
  (403, 406, 58, '2010-07-29 13:23:00', '2010-10-27 13:23:00', 'user_id347', '2010-07-29 13:23:40'),
  (404, 362, 57, '2010-07-29 13:25:00', '2010-10-27 13:25:00', 'user_id347', '2010-07-29 13:24:53'),
  (405, 362, 59, '2010-07-29 13:25:00', '2010-10-27 13:25:00', 'user_id347', '2010-07-29 13:25:15'),
  (406, 364, 57, '2010-07-29 13:25:00', '2010-10-27 13:25:00', 'user_id347', '2010-07-29 13:25:37'),
  (407, 364, 60, '2010-07-29 13:26:00', '2010-10-27 13:26:00', 'user_id347', '2010-07-29 13:25:53'),
  (408, 364, 58, '2010-07-29 13:26:00', '2010-10-27 13:26:00', 'user_id347', '2010-07-29 13:25:59'),
  (409, 414, 57, '2010-07-29 13:26:00', '2010-10-27 13:26:00', 'user_id347', '2010-07-29 13:26:14'),
  (410, 414, 59, '2010-07-29 13:26:00', '2010-10-27 13:26:00', 'user_id347', '2010-07-29 13:26:27'),
  (411, 414, 60, '2010-07-29 13:26:00', '2010-10-27 13:26:00', 'user_id347', '2010-07-29 13:26:30'),
  (412, 414, 58, '2010-07-29 13:26:00', '2010-10-27 13:26:00', 'user_id347', '2010-07-29 13:26:35'),
  (413, 408, 57, '2010-07-29 13:27:00', '2010-10-27 13:27:00', 'user_id347', '2010-07-29 13:26:49'),
  (414, 408, 60, '2010-07-29 13:27:00', '2010-10-27 13:27:00', 'user_id347', '2010-07-29 13:26:53'),
  (415, 408, 59, '2010-07-29 13:27:00', '2010-10-27 13:27:00', 'user_id347', '2010-07-29 13:26:56'),
  (420, 383, 57, '2010-07-29 13:29:00', '2010-10-27 13:29:00', 'user_id347', '2010-07-29 13:28:57'),
  (421, 383, 59, '2010-07-29 13:29:00', '2010-10-27 13:29:00', 'user_id347', '2010-07-29 13:29:03'),
  (422, 383, 60, '2010-07-29 13:29:00', '2010-10-27 13:29:00', 'user_id347', '2010-07-29 13:29:06'),
  (423, 383, 58, '2010-07-29 13:29:00', '2010-10-27 13:29:00', 'user_id347', '2010-07-29 13:29:17'),
  (424, 418, 57, '2010-07-29 13:29:00', '2010-10-27 13:29:00', 'user_id347', '2010-07-29 13:29:29'),
  (425, 418, 60, '2010-07-29 13:29:00', '2010-10-27 13:29:00', 'user_id347', '2010-07-29 13:29:37'),
  (426, 418, 59, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:29:40'),
  (427, 418, 58, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:29:44'),
  (428, 357, 57, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:29:55'),
  (429, 390, 57, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:30:21'),
  (430, 390, 59, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:30:27'),
  (431, 390, 60, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:30:30'),
  (432, 390, 58, '2010-07-29 13:30:00', '2010-10-27 13:30:00', 'user_id347', '2010-07-29 13:30:35'),
  (433, 393, 57, '2010-07-29 13:31:00', '2010-10-27 13:31:00', 'user_id347', '2010-07-29 13:30:45'),
  (438, 366, 57, '2010-07-29 13:31:00', '2010-10-27 13:31:00', 'user_id347', '2010-07-29 13:31:28'),
  (439, 405, 57, '2010-07-29 13:32:00', '2010-10-27 13:32:00', 'user_id347', '2010-07-29 13:31:46'),
  (440, 405, 58, '2010-07-29 13:32:00', '2010-10-27 13:32:00', 'user_id347', '2010-07-29 13:32:02'),
  (441, 397, 57, '2010-07-29 13:32:00', '2010-10-27 13:32:00', 'user_id347', '2010-07-29 13:32:27'),
  (442, 397, 59, '2010-07-29 13:32:00', '2010-10-27 13:32:00', 'user_id347', '2010-07-29 13:32:46'),
  (443, 397, 60, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:32:50'),
  (444, 397, 58, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:32:54'),
  (445, 405, 59, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:33:06'),
  (446, 405, 60, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:33:09'),
  (447, 382, 57, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:33:24'),
  (448, 382, 59, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:33:30'),
  (449, 382, 60, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:33:33'),
  (450, 382, 58, '2010-07-29 13:33:00', '2010-10-27 13:33:00', 'user_id347', '2010-07-29 13:33:36'),
  (452, 356, 57, '2010-07-29 13:34:00', '2010-10-27 13:34:00', 'user_id347', '2010-07-29 13:34:06'),
  (453, 356, 59, '2010-07-29 13:34:00', '2010-10-27 13:34:00', 'user_id347', '2010-07-29 13:34:15'),
  (454, 356, 58, '2010-07-29 13:34:00', '2010-10-27 13:34:00', 'user_id347', '2010-07-29 13:34:20'),
  (455, 373, 57, '2010-07-29 13:34:00', '2010-10-27 13:34:00', 'user_id347', '2010-07-29 13:34:29'),
  (456, 373, 59, '2010-07-29 13:34:00', '2010-10-27 13:34:00', 'user_id347', '2010-07-29 13:34:35'),
  (457, 373, 58, '2010-07-29 13:34:00', '2010-10-27 13:34:00', 'user_id347', '2010-07-29 13:34:40'),
  (458, 359, 57, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:34:51'),
  (459, 401, 57, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:35:05'),
  (460, 401, 59, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:35:08'),
  (461, 401, 60, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:35:12'),
  (462, 401, 58, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:35:16'),
  (463, 402, 59, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:35:28'),
  (464, 402, 60, '2010-07-29 13:35:00', '2010-10-27 13:35:00', 'user_id347', '2010-07-29 13:35:32'),
  (465, 403, 59, '2010-07-29 13:36:00', '2010-10-27 13:36:00', 'user_id347', '2010-07-29 13:35:55'),
  (466, 403, 60, '2010-07-29 13:36:00', '2010-10-27 13:36:00', 'user_id347', '2010-07-29 13:35:58'),
  (467, 402, 58, '2010-07-29 13:36:00', '2010-10-27 13:36:00', 'user_id347', '2010-07-29 13:36:15'),
  (471, 381, 59, '2010-07-29 13:37:00', '2010-10-27 13:37:00', 'user_id347', '2010-07-29 13:36:49'),
  (472, 381, 58, '2010-07-29 13:37:00', '2010-10-27 13:37:00', 'user_id347', '2010-07-29 13:36:53'),
  (473, 386, 60, '2010-07-29 13:37:00', '2010-10-27 13:37:00', 'user_id347', '2010-07-29 13:37:26'),
  (474, 389, 59, '2010-07-29 13:37:00', '2010-10-27 13:37:00', 'user_id347', '2010-07-29 13:37:38'),
  (475, 389, 60, '2010-07-29 13:38:00', '2010-10-27 13:38:00', 'user_id347', '2010-07-29 13:37:42'),
  (476, 389, 58, '2010-07-29 13:38:00', '2010-10-27 13:38:00', 'user_id347', '2010-07-29 13:37:46'),
  (477, 410, 59, '2010-07-29 13:38:00', '2010-10-27 13:38:00', 'user_id347', '2010-07-29 13:38:16'),
  (478, 410, 60, '2010-07-29 13:38:00', '2010-10-27 13:38:00', 'user_id347', '2010-07-29 13:38:18'),
  (479, 410, 58, '2010-07-29 13:38:00', '2010-10-27 13:38:00', 'user_id347', '2010-07-29 13:38:23'),
  (483, 412, 59, '2010-07-29 13:39:00', '2010-10-27 13:39:00', 'user_id347', '2010-07-29 13:39:22'),
  (484, 412, 60, '2010-07-29 13:39:00', '2010-10-27 13:39:00', 'user_id347', '2010-07-29 13:39:26'),
  (485, 370, 60, '2010-07-29 13:40:00', '2010-10-27 13:40:00', 'user_id347', '2010-07-29 13:40:35'),
  (486, 372, 59, '2010-07-29 13:41:00', '2010-10-27 13:41:00', 'user_id347', '2010-07-29 13:40:50'),
  (487, 372, 60, '2010-07-29 13:41:00', '2010-10-27 13:41:00', 'user_id347', '2010-07-29 13:40:53'),
  (488, 372, 58, '2010-07-29 13:41:00', '2010-10-27 13:41:00', 'user_id347', '2010-07-29 13:41:00'),
  (489, 407, 60, '2010-07-29 13:41:00', '2010-10-27 13:41:00', 'user_id347', '2010-07-29 13:41:19'),
  (490, 411, 59, '2010-07-29 13:41:00', '2010-10-27 13:41:00', 'user_id347', '2010-07-29 13:41:36'),
  (491, 411, 60, '2010-07-29 13:42:00', '2010-10-27 13:42:00', 'user_id347', '2010-07-29 13:41:39'),
  (492, 411, 58, '2010-07-29 13:42:00', '2010-10-27 13:42:00', 'user_id347', '2010-07-29 13:41:47'),
  (493, 361, 60, '2010-07-29 13:42:00', '2010-10-27 13:42:00', 'user_id347', '2010-07-29 13:42:00'),
  (494, 374, 59, '2010-07-29 13:42:00', '2010-10-27 13:42:00', 'user_id347', '2010-07-29 13:42:16'),
  (495, 374, 60, '2010-07-29 13:42:00', '2010-10-27 13:42:00', 'user_id347', '2010-07-29 13:42:19'),
  (496, 374, 58, '2010-07-29 13:42:00', '2010-10-27 13:42:00', 'user_id347', '2010-07-29 13:42:26'),
  (497, 363, 60, '2010-07-29 13:43:00', '2010-10-27 13:43:00', 'user_id347', '2010-07-29 13:42:42'),
  (498, 358, 60, '2010-07-29 13:43:00', '2010-10-27 13:43:00', 'user_id347', '2010-07-29 13:42:54'),
  (499, 409, 60, '2010-07-29 13:43:00', '2010-10-27 13:43:00', 'user_id347', '2010-07-29 13:43:04'),
  (500, 409, 59, '2010-07-29 13:43:00', '2010-10-27 13:43:00', 'user_id347', '2010-07-29 13:43:07'),
  (501, 409, 58, '2010-07-29 13:43:00', '2010-10-27 13:43:00', 'user_id347', '2010-07-29 13:43:12'),
  (502, 416, 59, '2010-07-29 13:43:00', '2010-10-27 13:43:00', 'user_id347', '2010-07-29 13:43:38'),
  (503, 416, 58, '2010-07-29 13:44:00', '2010-10-27 13:44:00', 'user_id347', '2010-07-29 13:43:41'),
  (507, 387, 60, '2010-07-29 13:44:00', '2010-10-27 13:44:00', 'user_id347', '2010-07-29 13:44:12'),
  (508, 387, 58, '2010-07-29 13:44:00', '2010-10-27 13:44:00', 'user_id347', '2010-07-29 13:44:15'),
  (511, 396, 58, '2010-07-29 13:49:00', '2010-10-27 13:49:00', 'user_id347', '2010-07-29 13:49:10'),
  (512, 385, 58, '2010-07-29 13:49:00', '2010-10-27 13:49:00', 'user_id347', '2010-07-29 13:49:23'),
  (514, 368, 56, '2010-08-03 08:14:00', '2011-01-01 15:00:00', 'user_id347', '2010-08-03 08:14:36'),
  (515, 391, 56, '2010-08-03 08:15:00', '2011-01-01 18:00:00', 'user_id347', '2010-08-03 08:15:12'),
  (516, 399, 56, '2010-08-03 08:16:00', '2011-01-01 18:00:00', 'user_id347', '2010-08-03 08:15:40'),
  (517, 10, 42, '2010-08-06 12:32:00', '2010-12-08 18:00:00', 'jcadmin', '2010-08-06 12:32:17'),
  (518, 163, 42, '2010-08-06 14:18:00', '2010-11-29 14:43:00', 'jcadmin', '2010-08-06 14:17:52'),
  (519, 103, 41, '2010-08-06 14:19:00', '2010-11-04 14:19:00', 'jcadmin', '2010-08-06 14:19:39'),
  (520, 99, 42, '2010-08-06 14:21:00', '2010-11-04 14:21:00', 'jcadmin', '2010-08-06 14:21:43'),
  (521, 99, 41, '2010-08-06 14:22:00', '2010-09-05 14:23:00', 'jcadmin', '2010-08-06 14:22:06'),
  (522, 14, 54, '2010-08-06 14:34:00', '2010-11-09 18:30:00', 'jcadmin', '2010-08-06 14:33:49'),
  (523, 11, 41, '2010-08-06 14:53:00', '2010-10-09 20:00:00', 'jcadmin', '2010-08-06 14:52:49'),
  (524, 37, 42, '2010-08-06 15:15:00', '2010-12-08 18:00:00', 'jcadmin', '2010-08-06 15:15:12'),
  (525, 15, 42, '2010-08-06 23:57:00', '2010-12-08 19:00:00', 'jcadmin', '2010-08-06 23:57:29'),
  (526, 51, 42, '2010-08-07 21:18:00', '2010-12-04 12:04:00', 'jcadmin', '2010-08-07 21:18:01'),
  (527, 7, 42, '2010-08-07 21:20:00', '2011-04-20 23:35:00', 'jcadmin', '2010-08-07 21:19:46'),
  (528, 56, 41, '2010-08-08 19:17:00', '2010-11-28 15:41:00', 'jcadmin', '2010-08-08 19:17:38'),
  (530, 81, 41, '2010-08-08 22:50:00', '2011-04-20 23:36:00', 'jcadmin', '2010-08-08 22:50:07'),
  (531, 5, 42, '2010-08-09 00:57:00', '2010-11-07 00:59:00', 'jcadmin', '2010-08-09 00:56:53'),
  (532, 197, 42, '2010-08-09 13:11:00', '2011-03-08 11:55:00', 'jcadmin', '2010-08-09 13:11:44'),
  (533, 56, 42, '2010-08-09 13:26:00', '2010-12-07 15:54:00', 'jcadmin', '2010-08-09 13:25:54'),
  (534, 1, 42, '2010-08-09 14:07:31', '2011-04-20 23:37:00', 'jcadmin', '2010-08-09 14:06:47'),
  (535, 12, 42, '2010-08-10 12:05:00', '2011-04-20 23:39:00', 'jcadmin', '2010-08-10 12:05:37'),
  (536, 72, 42, '2010-08-10 12:35:00', '2010-11-08 18:30:00', 'jcadmin', '2010-08-10 12:35:17'),
  (537, 7, 45, '2010-08-11 13:24:00', '2011-04-20 23:36:00', 'jcadmin', '2010-08-11 13:24:39'),
  (540, 256, 41, '2010-08-12 10:28:00', '2010-11-10 09:28:00', 'jcadmin', '2010-08-12 10:28:30'),
  (541, 89, 43, '2010-08-12 15:10:00', '2011-04-20 23:38:00', 'jcadmin', '2010-08-12 15:10:09'),
  (542, 89, 45, '2010-08-12 15:10:00', '2011-04-20 23:38:00', 'jcadmin', '2010-08-12 15:10:15'),
  (543, 52, 41, '2010-08-13 23:56:00', '2010-10-08 09:29:00', 'jcadmin', '2010-08-13 23:56:07'),
  (544, 102, 42, '2010-08-14 22:41:00', '2010-12-07 09:16:00', 'jcadmin', '2010-08-14 22:41:27'),
  (545, 7, 41, '2010-08-17 01:03:00', '2011-04-20 23:35:00', 'jcadmin', '2010-08-17 01:03:24'),
  (546, 7, 54, '2010-08-17 01:03:00', '2011-04-20 23:35:00', 'jcadmin', '2010-08-17 01:03:38'),
  (547, 4, 42, '2010-08-17 09:35:00', '2011-01-04 19:00:00', 'jcadmin', '2010-08-17 09:34:44'),
  (550, 91, 42, '2010-08-18 12:24:54', '2010-12-08 18:30:34', 'jcadmin', '2010-11-29 23:27:00'),
  (551, 91, 45, '2010-08-18 12:24:33', '2010-12-18 18:30:12', 'jcadmin', '2010-08-18 12:23:49'),
  (552, 45, 45, '2010-08-19 19:43:00', '2010-12-17 10:18:00', 'jcadmin', '2010-08-19 19:43:35'),
  (553, 95, 45, '2010-08-19 21:49:00', '2010-12-17 10:22:00', 'jcadmin', '2010-08-19 21:49:23'),
  (554, 46, 45, '2010-08-21 23:31:00', '2010-12-17 10:23:00', 'jcadmin', '2010-08-21 23:30:50'),
  (555, 60, 41, '2010-08-21 23:56:00', '2011-04-20 23:37:00', 'jcadmin', '2010-08-21 23:56:32'),
  (556, 60, 54, '2010-08-21 23:56:00', '2011-04-20 23:37:00', 'jcadmin', '2010-08-21 23:56:40'),
  (557, 2, 43, '2010-08-25 00:15:00', '2010-11-22 23:15:00', 'jcadmin', '2010-08-25 00:15:02'),
  (558, 3, 43, '2010-08-25 00:16:00', '2010-11-22 23:16:00', 'jcadmin', '2010-08-25 00:16:24'),
  (559, 177, 41, '2010-08-29 00:16:00', '2011-04-20 23:39:00', 'jcadmin', '2010-08-29 00:15:58'),
  (560, 177, 54, '2010-08-29 00:16:00', '2011-04-20 23:39:00', 'jcadmin', '2010-08-29 00:16:09'),
  (561, 92, 46, '2010-08-31 01:35:00', '2011-01-08 00:00:00', 'jcadmin', '2010-08-31 01:35:16'),
  (562, 57, 46, '2010-08-31 22:12:00', '2010-12-30 02:14:00', 'jcadmin', '2010-08-31 22:12:52'),
  (563, 102, 45, '2010-09-01 00:07:00', '2010-12-10 09:40:00', 'jcadmin', '2010-09-01 00:07:42'),
  (564, 102, 41, '2010-09-01 00:07:00', '2010-10-01 09:40:00', 'jcadmin', '2010-09-01 00:07:47'),
  (565, 58, 42, '2010-09-01 09:13:00', '2011-04-10 20:30:00', 'jcadmin', '2010-09-01 09:13:08'),
  (566, 58, 45, '2010-09-01 09:13:00', '2011-04-20 20:30:00', 'jcadmin', '2010-09-01 09:13:15'),
  (567, 29, 46, '2010-09-01 09:14:00', '2010-12-30 21:18:00', 'jcadmin', '2010-09-01 09:14:35'),
  (568, 154, 42, '2010-09-01 19:55:00', '2011-04-08 08:00:00', 'jcadmin', '2010-09-01 19:55:35'),
  (569, 151, 42, '2010-09-01 19:56:00', '2010-12-08 22:00:00', 'jcadmin', '2010-09-01 19:56:33'),
  (570, 124, 41, '2010-09-01 20:00:00', '2010-10-09 19:00:00', 'jcadmin', '2010-09-01 20:00:54'),
  (571, 127, 41, '2010-09-01 20:01:00', '2010-10-04 22:12:00', 'jcadmin', '2010-09-01 20:01:39'),
  (572, 114, 42, '2010-09-01 20:03:00', '2010-12-09 00:00:00', 'jcadmin', '2010-09-01 20:03:15'),
  (573, 96, 41, '2010-09-01 20:03:00', '2010-10-26 14:14:00', 'jcadmin', '2010-09-01 20:04:00'),
  (574, 111, 41, '2010-09-01 20:05:00', '2011-04-20 23:36:00', 'jcadmin', '2010-09-01 20:05:34'),
  (575, 111, 54, '2010-09-01 20:05:00', '2011-04-20 23:36:00', 'jcadmin', '2010-09-01 20:05:44'),
  (576, 127, 45, '2010-09-02 08:17:00', '2011-02-07 18:00:00', 'jcadmin', '2010-09-02 08:17:37'),
  (577, 120, 43, '2010-09-02 08:17:00', '2010-12-01 08:17:00', 'jcadmin', '2010-09-02 08:18:06'),
  (578, 120, 45, '2010-09-02 08:18:00', '2011-03-19 15:22:00', 'jcadmin', '2010-09-02 08:18:14'),
  (579, 124, 45, '2010-09-02 08:19:00', '2010-12-18 18:00:00', 'jcadmin', '2010-09-02 08:20:04'),
  (580, 212, 42, '2010-09-03 08:02:00', '2011-01-28 21:00:00', 'jcadmin', '2010-09-03 08:02:19'),
  (581, 351, 56, '2010-09-03 19:54:00', '2011-01-01 18:00:00', 'user_id423', '2010-09-03 19:54:15'),
  (582, 352, 56, '2010-09-03 19:54:00', '2011-01-01 18:00:00', 'user_id423', '2010-09-03 19:54:44'),
  (583, 369, 56, '2010-09-03 19:55:00', '2011-01-02 06:00:00', 'user_id423', '2010-09-03 19:56:11'),
  (584, 377, 56, '2010-09-03 19:56:00', '2011-01-02 08:00:00', 'user_id423', '2010-09-03 19:56:37'),
  (585, 415, 56, '2010-09-03 19:57:00', '2011-01-02 08:00:00', 'user_id423', '2010-09-03 19:57:10'),
  (586, 378, 56, '2010-09-03 19:57:00', '2011-01-02 11:59:00', 'user_id423', '2010-09-03 19:57:56'),
  (587, 380, 56, '2010-09-03 19:58:00', '2011-01-02 11:59:00', 'user_id423', '2010-09-03 19:58:28'),
  (588, 44, 45, '2010-09-04 05:43:00', '2010-12-17 09:17:00', 'jcadmin', '2010-09-04 05:44:14'),
  (589, 121, 42, '2010-09-04 19:49:00', '2010-12-03 22:11:00', 'jcadmin', '2010-09-04 19:49:48'),
  (590, 121, 45, '2010-09-04 19:49:00', '2010-12-13 22:11:00', 'jcadmin', '2010-09-04 19:49:55'),
  (591, 13, 42, '2010-09-06 18:32:00', '2010-12-05 21:03:00', 'jcadmin', '2010-09-06 18:32:47'),
  (592, 2, 42, '2010-09-07 06:36:00', '2011-02-10 20:00:00', 'jcadmin', '2010-09-07 06:36:27'),
  (593, 2, 45, '2010-09-07 06:36:00', '2010-12-06 06:36:00', 'jcadmin', '2010-09-07 06:36:35'),
  (594, 9, 45, '2010-09-07 11:06:00', '2011-04-20 23:38:00', 'jcadmin', '2010-09-07 11:06:53'),
  (595, 167, 41, '2010-09-08 11:27:00', '2010-10-08 14:53:00', 'jcadmin', '2010-09-08 11:27:37'),
  (596, 56, 45, '2010-09-08 11:28:00', '2010-12-17 15:54:00', 'jcadmin', '2010-09-08 11:28:21'),
  (597, 125, 42, '2010-09-09 01:31:00', '2010-12-08 00:31:00', 'jcadmin', '2010-09-09 01:31:24'),
  (598, 44, 42, '2010-09-09 01:51:00', '2011-04-10 00:00:00', 'jcadmin', '2010-09-09 01:50:47'),
  (599, 45, 42, '2010-09-09 02:26:00', '2010-12-08 01:26:00', 'jcadmin', '2010-09-09 02:26:26'),
  (600, 46, 43, '2010-09-09 02:37:00', '2010-12-08 01:37:00', 'jcadmin', '2010-09-09 02:37:41'),
  (601, 98, 42, '2010-09-09 02:45:00', '2010-12-08 01:45:00', 'jcadmin', '2010-09-09 02:44:47'),
  (602, 10, 41, '2010-09-09 15:25:00', '2010-12-08 14:25:00', 'jcadmin', '2010-09-09 15:25:35'),
  (603, 14, 42, '2010-09-09 15:26:00', '2010-12-08 23:55:00', 'jcadmin', '2010-09-09 15:26:54'),
  (604, 17, 41, '2010-09-09 15:28:00', '2011-02-23 15:33:00', 'jcadmin', '2010-09-09 15:28:12'),
  (605, 17, 43, '2010-09-09 15:28:00', '2011-04-24 16:33:00', 'jcadmin', '2010-09-09 15:28:20'),
  (606, 19, 42, '2010-09-09 15:32:00', '2011-04-28 00:56:00', 'jcadmin', '2010-09-09 15:31:58'),
  (607, 19, 45, '2010-09-09 15:32:00', '2011-01-04 13:15:00', 'jcadmin', '2010-09-09 15:32:04'),
  (608, 32, 42, '2010-09-09 15:34:00', '2010-12-09 18:01:00', 'jcadmin', '2010-09-09 15:33:49'),
  (609, 36, 43, '2010-09-09 15:35:00', '2010-12-08 18:00:00', 'jcadmin', '2010-09-09 15:35:27'),
  (610, 36, 45, '2010-09-09 15:35:00', '2010-12-18 18:00:00', 'jcadmin', '2010-09-09 15:35:33'),
  (611, 37, 45, '2010-09-09 15:36:00', '2010-12-18 18:00:00', 'jcadmin', '2010-09-09 15:36:30'),
  (612, 39, 45, '2010-09-09 15:37:00', '2011-02-07 18:00:00', 'jcadmin', '2010-09-09 15:37:26'),
  (613, 39, 41, '2010-09-09 15:37:00', '2011-02-23 20:30:00', 'jcadmin', '2010-09-09 15:37:32'),
  (614, 40, 41, '2010-09-09 15:39:00', '2011-02-09 13:41:00', 'jcadmin', '2010-09-09 15:39:13'),
  (615, 40, 45, '2010-09-09 15:39:00', '2011-04-20 14:41:00', 'jcadmin', '2010-09-09 15:39:19'),
  (616, 41, 41, '2010-09-09 15:40:00', '2011-02-09 13:42:00', 'jcadmin', '2010-09-09 15:40:21'),
  (617, 41, 45, '2010-09-09 15:40:00', '2011-04-20 14:42:00', 'jcadmin', '2010-09-09 15:40:27'),
  (618, 442, 43, '2010-09-10 00:18:00', '2010-12-08 23:18:00', 'jcadmin', '2010-09-10 00:18:17'),
  (619, 442, 41, '2010-09-10 00:18:00', '2010-12-08 23:18:00', 'jcadmin', '2010-09-10 00:18:23'),
  (620, 9, 41, '2010-09-10 00:19:00', '2011-04-20 23:38:00', 'jcadmin', '2010-09-10 00:19:03'),
  (621, 9, 43, '2010-09-10 00:19:00', '2011-04-20 23:38:00', 'jcadmin', '2010-09-10 00:19:10'),
  (622, 50, 42, '2010-09-14 11:03:00', '2010-12-13 10:03:00', 'jcadmin', '2010-09-14 11:02:49'),
  (624, 50, 41, '2010-09-22 20:26:00', '2011-02-10 16:03:00', 'jcadmin', '2010-09-22 20:26:28'),
  (625, 50, 43, '2010-09-22 20:26:00', '2010-12-21 19:26:00', 'jcadmin', '2010-09-22 20:26:36'),
  (626, 10, 43, '2010-09-28 08:25:00', '2010-12-27 07:25:00', 'jcadmin', '2010-09-28 08:24:58'),
  (627, 75, 42, '2010-09-30 08:48:00', '2011-04-25 01:00:00', 'jcadmin', '2010-09-30 08:48:22'),
  (628, 75, 45, '2010-09-30 08:48:00', '2011-05-05 01:00:00', 'jcadmin', '2010-09-30 08:48:30'),
  (629, 80, 41, '2010-10-01 12:09:00', '2010-12-30 11:09:00', 'jcadmin', '2010-10-01 12:08:44'),
  (630, 80, 54, '2010-10-01 12:09:00', '2010-12-30 11:09:00', 'jcadmin', '2010-10-01 12:08:52'),
  (631, 27, 41, '2010-10-01 12:10:00', '2010-12-30 11:10:00', 'jcadmin', '2010-10-01 12:10:42'),
  (632, 27, 54, '2010-10-01 12:11:00', '2010-12-30 11:11:00', 'jcadmin', '2010-10-01 12:10:49'),
  (633, 6, 43, '2010-10-01 14:30:00', '2010-12-30 13:30:00', 'jcadmin', '2010-10-01 14:30:34'),
  (634, 6, 45, '2010-10-01 14:30:00', '2010-12-30 13:30:00', 'jcadmin', '2010-10-01 14:30:42'),
  (635, 27, 45, '2010-10-01 14:40:00', '2010-12-30 13:40:00', 'jcadmin', '2010-10-01 14:40:33'),
  (637, 127, 42, '2010-10-02 00:22:00', '2011-01-28 18:00:00', 'jcadmin', '2010-10-02 00:21:47'),
  (638, 34, 42, '2010-10-02 01:16:00', '2010-12-31 00:16:00', 'jcadmin', '2010-10-02 01:16:34'),
  (639, 1, 46, '2010-10-29 18:15:00', '2011-04-20 23:37:00', 'jcadmin', '2010-12-03 11:30:00'),
  (640, 120, 42, '2010-11-08 23:46:00', '2011-03-09 14:22:00', 'jcadmin', '2010-11-08 23:46:06'),
  (641, 178, 43, '2010-11-12 01:31:00', '2011-02-10 01:31:00', 'jcadmin', '2010-11-12 01:31:03'),
  (642, 4, 43, '2010-11-13 01:16:00', '2011-02-11 01:16:00', 'jcadmin', '2010-11-13 01:16:07'),
  (643, 4, 45, '2010-11-13 01:16:00', '2011-02-11 01:16:00', 'jcadmin', '2010-11-13 01:16:14'),
  (644, 5, 43, '2010-11-13 01:17:00', '2011-02-11 01:17:00', 'jcadmin', '2010-11-13 01:17:22'),
  (645, 5, 45, '2010-11-13 01:17:00', '2011-04-20 14:41:00', 'jcadmin', '2010-11-13 01:17:29'),
  (646, 5, 41, '2010-11-13 01:17:00', '2011-02-09 13:41:00', 'jcadmin', '2010-11-13 01:17:43'),
  (647, 112, 42, '2010-11-13 01:37:00', '2011-04-24 16:34:00', 'jcadmin', '2010-11-13 01:37:26'),
  (648, 15, 41, '2010-11-14 01:32:00', '2011-02-14 00:09:00', 'jcadmin', '2010-11-14 01:32:17'),
  (649, 186, 41, '2010-11-15 11:30:00', '2011-02-23 19:30:00', 'jcadmin', '2010-11-15 11:29:45'),
  (652, 158, 54, '2010-11-30 14:45:36', '2011-02-28 14:45:15', 'jcadmin', '2010-11-30 14:46:00'),
  (653, 158, 43, '2010-11-30 14:47:00', '2011-02-28 14:47:00', 'jcadmin', '2010-11-30 15:25:00'),
  (654, 158, 57, '2010-11-30 14:48:00', '2011-02-28 14:48:00', 'jcadmin', '2010-11-30 14:48:00'),
  (657, 158, 42, '2010-11-13 15:31:00', '2011-02-28 15:31:00', 'jcadmin', '2010-11-30 15:31:00'),
  (658, 268, 43, '2010-11-30 15:31:00', '2011-02-28 15:31:00', 'jcadmin', '2010-11-30 15:31:00'),
  (660, 268, 45, '2010-11-30 15:32:00', '2011-02-28 15:32:00', 'jcadmin', '2010-11-30 15:32:00'),
  (665, 268, 41, '2010-11-30 15:45:00', '2011-02-28 15:45:00', 'jcadmin', '2010-11-30 15:45:00'),
  (667, 268, 54, '2010-11-30 15:52:00', '2011-02-28 15:52:00', 'jcadmin', '2010-11-30 15:52:00'),
  (669, 71, 41, '2010-11-30 23:50:00', '2011-02-28 23:50:00', 'jcadmin', '2010-11-30 23:51:00'),
  (677, 1, 41, '2010-01-01 13:28:00', '2011-04-20 23:37:00', 'jcadmin', '2010-12-01 23:35:00'),
  (678, 13, 45, '2010-12-10 10:46:00', '2011-03-10 10:46:00', 'jcadmin', '2010-12-10 10:45:42'),
  (679, 178, 41, '2010-12-10 11:09:00', '2011-03-10 11:09:00', 'jcadmin', '2010-12-10 11:08:44'),
  (680, 131, 42, '2010-12-10 11:10:00', '2011-03-10 11:10:00', 'jcadmin', '2010-12-10 11:09:50'),
  (681, 473, 42, '2010-12-12 23:52:00', '2011-03-12 23:52:00', 'jcadmin', '2010-12-12 23:52:28'),
  (682, 491, 42, '2010-12-15 11:29:00', '2011-03-15 12:29:00', 'jcadmin', '2010-12-15 11:29:15'),
  (683, 491, 41, '2010-12-15 11:29:00', '2011-03-15 12:29:00', 'jcadmin', '2010-12-15 11:56:04'),
  (684, 491, 45, '2010-12-15 11:56:00', '2011-03-15 12:56:00', 'jcadmin', '2010-12-15 11:56:09'),
  (685, 491, 43, '2010-12-15 11:56:00', '2011-03-15 12:56:00', 'jcadmin', '2010-12-15 11:56:13'),
  (686, 491, 46, '2010-12-15 11:56:00', '2011-03-15 12:56:00', 'jcadmin', '2010-12-15 11:56:22'),
  (687, 9, 54, '2011-01-10 14:27:00', '2011-04-20 23:38:00', 'jcadmin', '2011-01-10 14:27:23'),
  (688, 39, 42, '2011-01-11 15:01:00', '2011-04-11 17:02:00', 'jcadmin', '2011-01-11 15:01:06'),
  (689, 15, 46, '2011-01-11 15:05:00', '2011-05-11 17:06:00', 'jcadmin', '2011-01-11 15:05:19'),
  (690, 1, 45, '2010-01-01 00:00:00', '2020-01-01 00:00:00', 'jcadmin', '2011-02-11 11:47:08');



INSERT INTO tbl_position_qualification_matrix (id, position_identifier, qualification_id, customer_id) VALUES
  (1, 429, 42, 1),
  (3, 380, 42, 1),
  (4, 381, 41, 1),
  (5, 382, 43, 1),
  (6, 382, 46, 1),
  (7, 317, 42, 1),
  (8, 320, 41, 1),
  (9, 318, 41, 1),
  (10, 319, 41, 1),
  (11, 315, 42, 1),
  (12, 412, 42, 1),
  (13, 310, 42, 1),
  (14, 304, 42, 1),
  (15, 305, 41, 1),
  (16, 323, 42, 1),
  (17, 432, 42, 1),
  (18, 309, 42, 1),
  (19, 309, 45, 1),
  (20, 321, 42, 1),
  (21, 432, 45, 1),
  (22, 433, 45, 1),
  (23, 433, 42, 1),
  (24, 435, 42, 1),
  (26, 322, 42, 1),
  (27, 379, 43, 1),
  (28, 379, 45, 1),
  (32, 446, 41, 1),
  (35, 441, 41, 1),
  (37, 441, 43, 1),
  (38, 451, 43, 1),
  (39, 338, 42, 1),
  (40, 454, 41, 1),
  (41, 338, 45, 1),
  (42, 454, 54, 1),
  (43, 367, 43, 1),
  (44, 457, 42, 1),
  (47, 461, 55, 27),
  (48, 373, 42, 1),
  (49, 313, 41, 1),
  (50, 314, 41, 1),
  (51, 340, 41, 1),
  (52, 472, 56, 27),
  (53, 473, 56, 27),
  (54, 474, 56, 27),
  (55, 475, 56, 27),
  (56, 476, 56, 27),
  (57, 477, 56, 27),
  (58, 478, 56, 27),
  (59, 479, 56, 27),
  (60, 480, 56, 27),
  (61, 481, 56, 27),
  (62, 482, 56, 27),
  (63, 483, 56, 27),
  (64, 484, 56, 27),
  (65, 485, 56, 27),
  (66, 486, 56, 27),
  (67, 487, 56, 27),
  (68, 488, 56, 27),
  (69, 489, 56, 27),
  (70, 491, 56, 27),
  (71, 490, 56, 27),
  (72, 492, 56, 27),
  (73, 493, 56, 27),
  (74, 494, 56, 27),
  (75, 495, 56, 27),
  (76, 496, 56, 27),
  (77, 497, 56, 27),
  (78, 498, 56, 27),
  (79, 499, 56, 27),
  (80, 500, 56, 27),
  (81, 501, 56, 27),
  (82, 507, 46, 1),
  (83, 372, 41, 1),
  (84, 319, 45, 1),
  (85, 320, 45, 1),
  (86, 317, 45, 1),
  (87, 334, 42, 1),
  (88, 318, 45, 1),
  (89, 509, 48, 1),
  (90, 362, 43, 1),
  (91, 304, 41, 1),
  (92, 381, 45, 1),
  (93, 453, 41, 1),
  (94, 453, 45, 1);


My SQL SO FAR

SELECT 
  tbl_emp_qualifications.emp_id,
  tbl_emp_qualifications.qualification_id,
  tbl_position_qualification_matrix.position_identifier,
  tbl_position_qualification_matrix.qualification_id
FROM
  tbl_position_qualification_matrix
  LEFT OUTER JOIN tbl_emp_qualifications ON (tbl_position_qualification_matrix.qualification_id = tbl_emp_qualifications.qualification_id)
WHERE
  tbl_position_qualification_matrix.position_identifier = 381

Open in new window

0
Comment
Question by:Laurie_Omaha
  • 11
  • 8
  • 3
  • +1
23 Comments
 

Author Comment

by:Laurie_Omaha
Comment Utility
CREATE TABLE tbl_position_qualification_matrix (
  id                   int AUTO_INCREMENT NOT NULL,
  position_identifier  int,
  qualification_id     int,
  customer_id          int,
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = InnoDB;


and


CREATE TABLE tbl_emp_qualifications (
  record_id         int AUTO_INCREMENT NOT NULL,
  emp_id            int,
  qualification_id  int COMMENT 'Qualification Connnection to tbl_qualification',
  qual_from         datetime COMMENT 'Qualification Effective From',
  qual_til          datetime COMMENT 'qualified until.  Can be null due to some qualification never expire...typing, etc.',
  updated_by        varchar(50) NOT NULL,
  updated_at        timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  /* Keys */
  PRIMARY KEY (record_id)
) ENGINE = InnoDB;

CREATE UNIQUE INDEX Unique_EMPIDQUALQUALFROM
  ON tbl_emp_qualifications
  (emp_id, qualification_id, qual_from);
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
how about this..
ee.txt
0
 

Author Comment

by:Laurie_Omaha
Comment Utility
I didn't see a response? :)
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
Comment Utility
I had a timeout on original submission with the code attachment, it's there now
0
 

Author Comment

by:Laurie_Omaha
Comment Utility
That returns and empty set.  It should return some data.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 125 total points
Comment Utility
try this
SELECT a.EMP_ID,b.position_id
 from table1 as a
 cross join (select distinct position_id from table2) as B
 where not exists (select x.qualification_id
                     from table2 as x
                     left outer join table1 as y
                       on x.qualification_id=y.qualification_id
                      and y.emp_id=a.emp_id 
                    Where b.position_id=x.position_id
                      and y.emp_id is null
                   )
 group by a.emp_id,b.position_id

 order by 1,2

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
oops,  I cut-n-pasted the same table name


the main query should have
 from tbl_emp_qualifications

the subquery should have
   from tbl_position_qualification_matrix


I had the matrix table in both froms
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
here's a fixed version of my original
ee.txt
0
 

Author Comment

by:Laurie_Omaha
Comment Utility
Still an empty set on sdstuber.

(Query looks like this on lowfatspread?)
SELECT a.EMP_ID,b.position_identifier
 from tbl_emp_qualifications as a
 cross join (select distinct position_identifier from tbl_position_qualification_matrix) as B
 where not exists (select x.qualification_id
                     from tbl_emp_qualifications as x
                     left outer join tbl_position_qualification_matrix as y
                       on x.qualification_id=y.qualification_id
                      and y.emp_id=a.emp_id
                    Where b.position_identifier=x.position_identifier
                      and y.emp_id is null
                   )
 group by a.emp_id,b.position_id

 order by 1,2

0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
returns 24 rows for position 381
SELECT a.EMP_ID,b.position_identifier
 from tbl_emp_qualifications as a
 cross join (select distinct position_identifier from tbl_position_qualification_matrix) as B
 where not exists (select x.qualification_id
                     from tbl_position_qualification_matrix as x
                     left outer join tbl_emp_qualifications as y
                       on x.qualification_id=y.qualification_id
                      and y.emp_id=a.emp_id 
                    Where b.position_identifier=x.position_identifier
                      and y.emp_id is null
                   )
  and b.position_identifier = 381
 group by a.emp_id,b.position_identifier

 order by 1,2

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
or this if you are going for individual positions
SELECT a.EMP_ID
 from tbl_emp_qualifications as a 
 where not exists (select x.qualification_id
                     from tbl_position_qualification_matrix as x
                     left outer join tbl_emp_qualifications as y
                       on x.qualification_id=y.qualification_id
                      and y.emp_id=a.emp_id 
                    Where x.position_identifier=381
                      and y.emp_id is null
                   )
  
 group by a.emp_id
 order by 1
/*

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> Still an empty set on sdstuber.

based on the data you have posted, isn't empty set correct?


You have 9 qualifications,  nobody in your emp table has more than 1 qualification

0
 

Author Comment

by:Laurie_Omaha
Comment Utility
Let me recheck the data.  emp_id (1) should have two qualifications that match 41 and 45.

position 381 should "require" qualification_id 41 and qualification_id 45.

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
ah -

You don't want  emp with all qualifications

you want emp with all qualifications "PER POSITION"
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If my previous post is correct how are positions tied to an emp?

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
oh jeez, I just saw that I never put the

where position_identifier = 381

in my subquery!!


HAVING COUNT(DISTINCT qualification_id) =
             (SELECT COUNT(DISTINCT qualification_id) FROM tbl_position_qualification_mat
             where position_identifier = 381 )


but, I still only see 1 qualification per emp, so even restricting it to 381 that's 2 qualifications needed
so empty set seems correct
0
 

Author Comment

by:Laurie_Omaha
Comment Utility
Thanks to the both of you for your help already!

I will explain a little better in hopes of making it easier to understand.

tbl_emp_qualifications has an emp_id, which numerous records of qualification_ids.  So an employee can have multiple qualifications_ids.

The table tbl_position_qualification_matrix has a position_identifier and a qualification_id that is required to 'work' this position.

So, if i need a list of people who are qualified to work a position, i want to be able to get everyone on the list (tbl_emp_qualifications) where they are qualified for each of the qualifification_id's in the tbl_position_qualification_matrix.

Hope that makes sense.

I am attaching a subset of the tables with one a few records to make it easier to use.


CREATE TABLE tbl_emp_qualifications (
  record_id         int AUTO_INCREMENT NOT NULL,
  emp_id            int,
  qualification_id  int COMMENT 'Qualification Connnection to tbl_qualification',
  qual_from         datetime COMMENT 'Qualification Effective From',
  qual_til          datetime COMMENT 'qualified until.  Can be null due to some qualification never expire...typing, etc.',
  updated_by        varchar(50) NOT NULL,
  updated_at        timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  /* Keys */
  PRIMARY KEY (record_id)
) ENGINE = InnoDB;

CREATE UNIQUE INDEX Unique_EMPIDQUALQUALFROM
  ON tbl_emp_qualifications
  (emp_id, qualification_id, qual_from);



INSERT INTO tbl_emp_qualifications (record_id, emp_id, qualification_id, qual_from, qual_til, updated_by, updated_at) VALUES
  (677, 1, 41, '2010-01-01 13:28:00', '2011-04-20 23:37:00', 'jcadmin', '2010-12-01 23:35:00'),
  (534, 1, 42, '2010-08-09 14:07:31', '2011-04-20 23:37:00', 'jcadmin', '2010-08-09 14:06:47'),
  (229, 1, 43, '2010-05-05 00:50:00', '2011-04-20 23:37:00', 'jcadmin', '2010-05-05 00:50:34'),
  (690, 1, 45, '2010-01-01 00:00:00', '2020-01-01 00:00:00', 'jcadmin', '2011-02-11 11:47:08'),
  (639, 1, 46, '2010-10-29 18:15:00', '2011-04-20 23:37:00', 'jcadmin', '2010-12-03 11:30:00'),
  (314, 2, 41, '2010-07-18 01:02:00', '2010-08-15 19:30:00', 'jcadmin', '2010-07-18 01:02:27'),
  (592, 2, 42, '2010-09-07 06:36:00', '2011-02-10 20:00:00', 'jcadmin', '2010-09-07 06:36:27'),
  (557, 2, 43, '2010-08-25 00:15:00', '2010-11-22 23:15:00', 'jcadmin', '2010-08-25 00:15:02'),
  (593, 2, 45, '2010-09-07 06:36:00', '2010-12-06 06:36:00', 'jcadmin', '2010-09-07 06:36:35'),
  (344, 3, 41, '2010-07-23 01:30:00', '2010-11-28 14:38:00', 'jcadmin', '2010-07-23 01:30:31'),
  (558, 3, 43, '2010-08-25 00:16:00', '2010-11-22 23:16:00', 'jcadmin', '2010-08-25 00:16:24'),
  (547, 4, 42, '2010-08-17 09:35:00', '2011-01-04 19:00:00', 'jcadmin', '2010-08-17 09:34:44'),
  (642, 4, 43, '2010-11-13 01:16:00', '2011-02-11 01:16:00', 'jcadmin', '2010-11-13 01:16:07'),
  (643, 4, 45, '2010-11-13 01:16:00', '2011-02-11 01:16:00', 'jcadmin', '2010-11-13 01:16:14'),
  (646, 5, 41, '2010-11-13 01:17:00', '2011-02-09 13:41:00', 'jcadmin', '2010-11-13 01:17:43'),
  (531, 5, 42, '2010-08-09 00:57:00', '2010-11-07 00:59:00', 'jcadmin', '2010-08-09 00:56:53'),
  (644, 5, 43, '2010-11-13 01:17:00', '2011-02-11 01:17:00', 'jcadmin', '2010-11-13 01:17:22'),
  (645, 5, 45, '2010-11-13 01:17:00', '2011-04-20 14:41:00', 'jcadmin', '2010-11-13 01:17:29'),
  (633, 6, 43, '2010-10-01 14:30:00', '2010-12-30 13:30:00', 'jcadmin', '2010-10-01 14:30:34'),
  (634, 6, 45, '2010-10-01 14:30:00', '2010-12-30 13:30:00', 'jcadmin', '2010-10-01 14:30:42'),
  (545, 7, 41, '2010-08-17 01:03:00', '2011-04-20 23:35:00', 'jcadmin', '2010-08-17 01:03:24'),
  (527, 7, 42, '2010-08-07 21:20:00', '2011-04-20 23:35:00', 'jcadmin', '2010-08-07 21:19:46'),
  (537, 7, 45, '2010-08-11 13:24:00', '2011-04-20 23:36:00', 'jcadmin', '2010-08-11 13:24:39'),
  (546, 7, 54, '2010-08-17 01:03:00', '2011-04-20 23:35:00', 'jcadmin', '2010-08-17 01:03:38'),
  (620, 9, 41, '2010-09-10 00:19:00', '2011-04-20 23:38:00', 'jcadmin', '2010-09-10 00:19:03'),
  (348, 9, 42, '2010-07-23 01:31:00', '2011-04-20 23:38:00', 'jcadmin', '2010-07-23 01:31:39'),
  (621, 9, 43, '2010-09-10 00:19:00', '2011-04-20 23:38:00', 'jcadmin', '2010-09-10 00:19:10'),
  (594, 9, 45, '2010-09-07 11:06:00', '2011-04-20 23:38:00', 'jcadmin', '2010-09-07 11:06:53'),
  (687, 9, 54, '2011-01-10 14:27:00', '2011-04-20 23:38:00', 'jcadmin', '2011-01-10 14:27:23');



--now the table for the positions

CREATE TABLE tbl_position_qualification_matrix (
  id                   int AUTO_INCREMENT NOT NULL,
  position_identifier  int,
  qualification_id     int,
  customer_id          int,
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = InnoDB;



INSERT INTO tbl_position_qualification_matrix (id, position_identifier, qualification_id, customer_id) VALUES
  (4, 381, 41, 1),
  (92, 381, 45, 1);

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
with that data you should see emp id 1
ee.txt
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
and 2, 5, 7 also
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
Comment Utility
Lowfatspread should work. You can also check this query.
SELECT DISTINCT t1.emp_id 
  FROM (  SELECT t2.emp_id, 
                 t1.position_identifier, 
                 COUNT(DISTINCT t1.qualification_id) cnt 
            FROM tbl_position_qualification_matrix t1 
                 JOIN tbl_emp_qualifications t2 
                   ON t1.qualification_id = t2.qualification_id 
        GROUP BY t2.emp_id, 
                 t1.position_identifier) t1 
       JOIN (  SELECT position_identifier, 
                      COUNT(DISTINCT qualification_id) cnt 
                 FROM tbl_position_qualification_matrix 
             GROUP BY position_identifier) t2 
         ON t1.cnt = t2.cnt 
            AND t1.position_identifier = t2.position_identifier 
 WHERE t1.position_identifier = 381

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
yikes, I can't believe all the mistakes I've made on this

sorry for the confusion



ee.txt
0
 

Author Comment

by:Laurie_Omaha
Comment Utility
I got 3 and 6.
example.jpg
0
 

Author Closing Comment

by:Laurie_Omaha
Comment Utility
Thanks to all of you.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The article explains the protocols and technology which is involved when two computers on different TCP/IP networks communicate with each other. In the diagram, a router is used to segregate two networks. The networks are 192.168.1.0/24 and 192…
Creating an OSPF network that automatically (dynamically) reroutes network traffic over other connections to prevent network downtime.
Viewers will learn how to properly install and use Secure Shell (SSH) to work on projects or homework remotely. Download Secure Shell: Follow basic installation instructions: Open Secure Shell and use "Quick Connect" to enter credentials includi…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now