SELECT Help

--00163630fc059accac049b60d131
Content-Type: text/plain; charset=ISO-8859-1

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

--00163630fc059accac049b60d131--

RE: SELECT Help

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@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=gcdmg-mysql-2@m.gmane.org

Re: SELECT Help

--00163630fc050b66eb049b7232db
Content-Type: text/plain; charset=ISO-8859-1

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 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@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
>
>

--00163630fc050b66eb049b7232db--