List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:February 4 2011 10:17am
Subject:Re: SELECT Help
View as plain text  
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_ard@stripped> 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.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