Hi Travis, That query kind of gives me the desired result. However, if is showing me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6, 2010-12-20 22:17:13, which is when they changed teams. Any thoughts ?
Cheers Neil On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard <travis_...@hotmail.com> wrote: > 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 > >
