Re: Tough queries
David Legault wrote:
> Thanks for the reply, but I think you misinterpreted what I'm looking
> for here.
>
> For the first query I want to be able to get a row record of (in one
> query possibly)
>
> team_name for first team (team_id1)
> team_name for second team (team_id2)
> and each of these team points
>
> for a list of N games retreived (and NULL values for the scores if
> there are no points).
select g.game_id,
concat( t1.name, ' (', count(p1.point_id), ') - ',
t2.name, ' (', count(p2.point_id), ')' )
from games g, teams t1, teams t2, points p1, points p2
where g.team_id1 = t1.team_id
and g.team_id2 = t2.team_id
and g.team_id1 = p1.team_id
and g.team_id2 = p2.team_id
group by g.game_id, t1.name, t2.name
you need to include game_id to handle the case where two teams play more
than once, don't want to roll the scoring for the two teams together.
I'm assuming that for game score you're looking for just points scored,
not assists and other stuff.
> Same kind of query for the player stats where I'd retreive these infos
> on each row record :
>
> player_name
> total goals
> total assists
> total points
> sorted by total points DESC
select p.player_name,
count( p1.point_id ) as goals,
count( p2.point_id ) + count( p3.point_id ) as assists,
count( p1.point_id ) + count( p2.point_id ) + count( p3.point_id
) as points
from players p, points p1, points p2, points p3
where p.player_id = p1.goal_player_id
and p.player_id = p2.pass_player_id1
and p.player_id = p3.pass_player_id2
group by p.player_name
order by 4 desc
do we need to handle the double-counting case in which the scoring
player also has the first pass? that starts to get tricky; at that
point I'd start pulling the data and handling it programmatically.
heck, I'd probably handle this programmatically as well, but handling it
in sql made for a nice diversion... ;-)
how about players with the same name? john smith or such... at that
point I suppose you could include player_id in the select, just like I
included game_id above...
note, I haven't actually tested the above. I'm pretty lazy and didn't
want to make up test data. I've probably got a typo somewhere, but i
think it's probably pretty close.
- mark
> Thanks
>
> David
>
> [EMAIL PROTECTED] wrote:
>
>> Hi,
>> for the first query,
>> select concat(team_id,' (',sum(points),')') from games,points
>> where games.game_id=points.game_id
>> and games.team_id1=points.team_id
>> group by team_id
>>
>> can solve the problem.
>>
>> For the second, join players and points.
>>
>>
>> Mathias
>>
>> Selon David Legault <[EMAIL PROTECTED]>:
>>
>>
>>> Hello,
>>>
>>> I'm a regular user of MySQL but lately on a personal project I've run
>>> into some very complexe query management and am a little bit
>>> confused on
>>> how to get it working. This is a hockey league stats website
>>> application.
>>>
>>> I have 4 tables with the following columns:
>>>
>>> teams -> team_id | team_name
>>> players -> player_id | team_id | player_name
>>> games -> game_id | team_id1 | team_id2
>>> points-> point_id | game_id | team_id | goal_player_id |
>>> pass_player_id1
>>> | pass_player_id2
>>>
>>> The kind of queries I'd like to perform would be (if possible in one
>>> query or a subset of queries) something to generate the following as a
>>> list of N games with the scores (if the game was played and team name)
>>> which would use the games, teams and points tables.
>>>
>>> Desired Output
>>>
>>> Team A (2) - Team B (7)
>>> Team D (3) - Team C (1)
>>> ...
>>>
>>> Thus, it needs to retreive the team names, and total score for each
>>> game
>>> that I want to list (using other criteria not essential in the example)
>>>
>>> Another Query would be to have the points of each player listed for a
>>> team (in ORDER DESC or total points):
>>>
>>> Team C:
>>>
>>> Player | Goals | Assists | Points
>>>
>>> AA 8 1 9
>>> BB 5 3 8
>>> CC 3 2 5
>>> DD 1 2 3
>>>
>>>
>>> If you could explain a little bit how each query answer you provide
>>> works, I'd like it. If you have any tutorials or good articles about
>>> such complexe queries, I'd be thankfull to be able to read them to
>>> help me.
>>>
>>> Thanks
>>>
>>> David
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>>
>>>
>>
>>
>>
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[4.0] latin1 accented characters and fulltext
I've been playing around with fulltext searching in 4.0, and I ran into
the following weirdness with accented characters.
| version | 4.0.14
|
character_set | latin1
in my entire collection, there is just one row with the keyword
'angélica' with the accent, several others without. a fulltext search
for 'angélica' returns all of them. it's almost as if mysql knows what
the base unaccented character is, and is performing some normalization
before searching. but I couldn't find this documented anywhere (tried a
search for 'accented character fulltext', nothing looked relevant).
I don't think I _mind_ the behavior. In fact it may actually save me
jumping through some hoops in order to meet functional requirements, but
I just didn't _expect_ it...
- mark
drop table test_search;
create table test_search (
artist_id integer not null,
lang_code char(5) not null,
name varchar(255) not null,
keywords varchar(255) not null,
primary key ( artist_id, lang_code ),
fulltext ( keywords )
) type=myisam;
insert into test_search values ( 740273, 'en-us', 'Angelica', 'Angelica' );
insert into test_search values ( 783679, 'en-us', 'Angelica Garcia',
'Angelica Garcia' );
insert into test_search values ( 756774, 'en-us', 'Angélica Vale',
'Angelica Angélica Vale' );
insert into test_search values ( 751119, 'en-us', 'Electric Junkyard',
'Electric Junkyard' );
insert into test_search values ( 774590, 'en-us', 'Moncho', 'Moncho' );
select artist_id,lang_code,name,keywords
FROM test_search
WHERE match(keywords) against ('angélica' in boolean mode)
ORDER BY name asc
+---+---+-++
| artist_id | lang_code | name| keywords |
+---+---+-++
|740273 | en-us | Angelica| Angelica |
|783679 | en-us | Angelica Garcia | Angelica Garcia|
|756774 | en-us | Angélica Vale | Angelica Angélica Vale |
+---+---+-++
3 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [4.1.1] 1062 errors on non-unique index during data load
Victoria Reznichenko wrote: [snip] Could you create repeatable test case (table structure (output of SHOW CREATE TABLE) and text file with data that will be enough to reproduce the above behavior)? I'll see if I can get something generic to error out. there's pretty much no way I'll be able to send my actual data file though... - mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [4.1.1] 1062 errors on non-unique index during data load
heh, sometimes the obvious is right in front of you... I still can't get the full dataset to load with indexes on the table, but I _can_ apply indexes after the load. the indexes apply cleanly, so it doesn't seem to have been a data issue. in any case, I can continue prototyping. it remains to be seen whether this will crop back up during incremental update of the already filled (and indexed) table, but I've got a good bit of coding to do before I'll have an answer to that question... - mark mark warren bracher wrote: In the last few days I've been doing some prototyping on mysql 4.1.1 (mainly because I want fulltext indexes against utf-8 data). I have a table, artists_search_A, in which I want to load ~100K records. My load process will routinely load 14783 records successfully. After 14783 inserts, any insert attempt results in a 1062 error, duplicate entry; specifically, Duplicate entry 'en-us' for key 2 error 1062 recorded [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[4.1.1] 1062 errors on non-unique index during data load
In the last few days I've been doing some prototyping on mysql 4.1.1 (mainly because I want fulltext indexes against utf-8 data). I have a table, artists_search_A, in which I want to load ~100K records. My load process will routinely load 14783 records successfully. After 14783 inserts, any insert attempt results in a 1062 error, duplicate entry; specifically, Duplicate entry 'en-us' for key 2 error 1062 recorded That's on the lang_code column. Looking through my source data, I have precisely 4 distinct lang_codes, and at this point _only_ en-us data have been loaded. The prior 14783 records should not have loaded successfully if the index were truly unique. A quick check in the output of 'show index' (pasted below) shows the lang_code index to be non-unique. Out of ~20 separate attempts in the last few days (each time I tweak something hoping to find a remedy), in only one case did all the data load. Sadly, I have no idea what was unique about that run. All other attempts bomb out after precisely 14783 records... Anyone else encountering this? It sounds a lot like bug 2401 http://bugs.mysql.com/bug.php?id=2401 except that it happens even if no other thread accesses the table during the load, and it always occurs after exactly the same number of inserts. - mark mysql> desc artists_search_A; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | artist_id| int(11) | | PRI | 0 | | | lang_code| varchar(5) | | PRI | | | | name | varchar(128) | | MUL | | | | major_cat| smallint(6) | | MUL | 0 | | | minor_cat| smallint(6) | | MUL | 0 | | | events_scheduled | char(1) | | MUL | n | | | unmapped | char(1) | YES | MUL | NULL| | | team | char(1) | YES | MUL | NULL| | | dma_ids | varchar(255) | YES | MUL | NULL| | | national_ids | varchar(64) | YES | MUL | NULL| | | keywords | text | YES | MUL | NULL| | | tmol_modified| timestamp| YES | | NULL| | +--+--+--+-+-+---+ 12 rows in set (0.00 sec) This is the second index on lang_code, snipped from show index... +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | artists_search_A | 0 | PRIMARY |1 | artist_id| A |NULL | NULL | NULL | | BTREE | | | artists_search_A | 0 | PRIMARY |2 | lang_code| A | 14783 | NULL | NULL | | BTREE | | | artists_search_A | 1 | lang_code|1 | lang_code| A |NULL | NULL | NULL | | BTREE | | [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
