Something like this might help you find all of the times where your user_id switched to a different team_id:
select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -----Original Message----- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
