MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:February 3 2011 10:32pm
Subject:RE: SELECT Help
View as plain text  
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.tompkins@stripped] 
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

Thread
SELECT HelpTompkins Neil3 Feb
  • RE: SELECT HelpTravis Ard3 Feb
    • Re: SELECT HelpTompkins Neil4 Feb