[H] Advanced mysql knowledge required

s.a.y

Croatia3840 Posts

April 29 2010 11:31 GMT

Hello, i require some advanced mysql knowledge (which i am sure exists withing tl.net)

I am currently arranging the winnings page on tl.net wiki and updating takes quite a while. First time it took my about 4 hours.
Second time, i made the entire thing in ms excel, but it cut down the updating time to 2 hours, which is still too much.

After that, i made a database on my PC and it works (it has all the tournaments and players listed). Now i need a query that will combine it all so i can directly implement it into wiki.

So, i have two tables - players and tournaments.
Players table consists of pid (player id, unique), nick and others stuff.
Tournament table consists of tid (tournament id, unique), date, tourney name, winnings and pid (connection to the player table).

I managed to do simple queries to get info how much a single player won, or what's the total sum of winnings in tournaments.

Now, i need the complicated one. Query that would sort players by tournament winnings.

halp!

FaCE_1

Canada6186 Posts

April 29 2010 12:09 GMT

if no one is able to answer you before the week-end, I can try to do it. currently to much work to do at job T.T

i'll keep it in my fav and come back saturday to see if you had progress or not.

s.a.y

Croatia3840 Posts

April 29 2010 12:14 GMT

On April 29 2010 21:09 FaCE_1 wrote:
if no one is able to answer you before the week-end, I can try to do it. currently to much work to do at job T.T

i'll keep it in my fav and come back saturday to see if you had progress or not.

thanks

much appreciated.

IronWolf

South Africa315 Posts

April 29 2010 12:20 GMT

select player.id, player.nick, player.name, tournament.id, tournament.name, sum(tournament.winnings) as totalwinnigsfortournament
from player inner join tournament
group by player.id, player.nick, player.name, tournament.id, tournament.name
order by totalwinnigsfortournament

Not too 100% on the order by...but it is something like that. If you send me a db dump/export i can debug that qry for you.

Shade692003

Canada702 Posts

April 29 2010 12:46 GMT

On April 29 2010 21:20 inforsomechop wrote:
select player.id, player.nick, player.name, tournament.id, tournament.name, sum(tournament.winnings) as totalwinnigsfortournament
from player inner join tournament
ON tournament.pid = player.pid
group by player.id, player.nick, player.name, tournament.id, tournament.name
order by totalwinnigsfortournament

Not too 100% on the order by...but it is something like that. If you send me a db dump/export i can debug that qry for you.

Edit: Also, keep in mind by doing a inner join you will not get players who has 0 winning. So if you want to rank every player, do a left join, and order by totalwinningsfortournament, player.name, player.nick

IronWolf

South Africa315 Posts

April 29 2010 13:04 GMT

agreed - thanks for fixing that.

s.a.y

Croatia3840 Posts

April 29 2010 14:52 GMT

doesn't work the way i wanted.

Now it displays the single largest winnings by a player on a single tournament and sorts by it, not the total sum of a player.

+ Show Spoiler +

pid nick tid name ukupno
10 HayprO 22 CraftCup EU 50
3 Dimaga 27 Craft Cup EU 50
12 HuK 29 Craft Cup US 50
12 HuK 30 Craft Cup US 50
1 LucifroN 35 GosuCup Invitational 2nd 50
9 Nazgul 36 GosuCup Invitational 3rd 50
9 Nazgul 43 TeamLiquid SC2 Invitational 3rd 50
3 Dimaga 1 Zotac cup 135
3 Dimaga 3 Zotac Cup 135
6 DeMusliM 4 Zotac Cup 135
10 HayprO 5 Zotac Cup 135
9 Nazgul 9 Bescene Benelux 135
3 Dimaga 12 SC2TV.ru Invitational 1 135
3 Dimaga 13 SC2TV.ru Invitational 2 135
5 White-Ra 20 Go4SC2 ESL Saturday 135
11 TheLittleOne 31 Go4SC2 ESL Saturday 135
6 DeMusliM 33 Go4SC2 ESL Saturday 135
10 HayprO 34 GosuCup Invitational 1st 150
11 TheLittleOne 42 TeamLiquid SC2 Invitational 2nd 150
5 White-Ra 49 Assem vs White-Ra showmatch 150
4 Machine 47 Huk vs Machine Showmatch 200
1 LucifroN 11 ESL TV Pro Invite Cup 270
5 White-Ra 10 TeamLiquid SC2 Invitational 1 300
4 Machine 40 Hello, Goodbye 2nd 300
2 Idra 8 Hello, Goodbye 1st 550
1 LucifroN 18 Go4SC2 ESL March Finals 680

That's the result i get now.

tarpman

Canada723 Posts

April 29 2010 14:58 GMT

You probably want a cross join rather than an inner or left join, so that every combination of player and tournament gets represented in the result. Otherwise the above query seems OK.

BTW this isn't really "advanced", nor is it MySQL specific...

EDIT: er. what I meant was "select from player, tournament" rather than using an explicit JOIN... forgot that cross join is in fact an SQL command... >_>

s.a.y

Croatia3840 Posts

April 29 2010 15:06 GMT

On April 29 2010 23:58 tarpman wrote:
You probably want a cross join rather than an inner or left join, so that every combination of player and tournament gets represented in the result. Otherwise the above query seems OK.

BTW this isn't really "advanced", nor is it MySQL specific...

EDIT: er. what I meant was "select from player, tournament" rather than using an explicit JOIN... forgot that cross join is in fact an SQL command... >_>

replacing inner join with cross join doesn't help either

Shade692003

Canada702 Posts

April 29 2010 15:25 GMT

#10

Maybe it's your table logic? This should be a 0.N 0.N relation, which means there should be an "intersection" table to link a specific player to a specific tournament, no?

s.a.y

Croatia3840 Posts

April 29 2010 15:32 GMT

#11

not sure. Tables are quite simple, in 2NF format.

here's the sql code that i exported (data is not complete, still got some players missing)

+ Show Spoiler +

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 29, 2010 at 03:31 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `win`
--

-- --------------------------------------------------------

--
-- Table structure for table `player`
--

CREATE TABLE IF NOT EXISTS `player` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`nick` text NOT NULL,
`country` text NOT NULL,
`server` enum('EU','NA','Asia') NOT NULL,
`race` enum('p','z','t','r') NOT NULL,
UNIQUE KEY `pid` (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `player`
--

INSERT INTO `player` (`pid`, `nick`, `country`, `server`, `race`) VALUES
(5, 'White-Ra', 'Ukraine', 'EU', 'p'),
(1, 'LucifroN', 'Spain', 'EU', 't'),
(2, 'Idra', 'USA', 'NA', 'z'),
(3, 'Dimaga', 'Ukraine', 'EU', 'p'),
(4, 'Machine', 'USA', 'NA', 'z'),
(6, 'DeMusliM', 'UK', 'EU', 't'),
(7, 'GoOdy', 'Germany', 'EU', 'p'),
(9, 'Nazgul', 'Netherlands', 'EU', 'p'),
(10, 'HayprO', 'Sweden', 'EU', 'z'),
(11, 'TheLittleOne', 'Germany', 'EU', 'r'),
(12, 'HuK', 'Australia', 'NA', 'p');

-- --------------------------------------------------------

--
-- Table structure for table `tournament`
--

CREATE TABLE IF NOT EXISTS `tournament` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`name` text NOT NULL,
`prize_money` decimal(10,0) NOT NULL,
`winner` text NOT NULL,
`second` text NOT NULL,
`pid` int(11) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

--
-- Dumping data for table `tournament`
--

INSERT INTO `tournament` (`tid`, `date`, `name`, `prize_money`, `winner`, `second`, `pid`) VALUES
(1, '2010-02-28', 'Zotac cup', '135', 'Dimaga', '', 3),
(2, '2010-03-07', 'Zotac Cup', '135', 'Orly', '', 0),
(3, '2010-03-14', 'Zotac Cup', '135', 'Dimaga', '', 3),
(4, '2010-03-21', 'Zotac Cup', '135', 'DeMusliM', '', 6),
(5, '2010-03-28', 'Zotac Cup', '135', 'HayprO', '', 10),
(6, '2010-04-04', 'Zotac Cup', '135', 'GoOdy', '', 0),
(7, '2010-04-11', 'Zotac Cup', '135', 'Mana', '', 0),
(8, '2010-04-10', 'Hello, Goodbye 1st', '550', 'Idra', '', 2),
(9, '2010-04-10', 'Bescene Benelux', '135', 'Nazgul', '', 9),
(10, '2010-03-27', 'TeamLiquid SC2 Invitational 1', '300', 'White-Ra', '', 5),
(11, '2010-04-19', 'ESL TV Pro Invite Cup', '270', 'LucifroN', '', 1),
(12, '2010-02-27', 'SC2TV.ru Invitational 1', '135', 'Dimaga', '', 3),
(13, '2010-03-15', 'SC2TV.ru Invitational 2', '135', 'Dimaga', '', 3),
(14, '2010-03-13', 'PlayXP Weekly Tournament', '135', 'Check', 'ZenioWeRRa', 0),
(15, '2010-03-20', 'PlayXP Weekly Tournament', '135', 'AnyPro', '', 0),
(16, '2010-03-27', 'PlayXP Weekly Tournament', '45', 'Lui', '', 0),
(17, '2010-04-10', 'CraftCup (US)', '50', 'Levin', '', 0),
(18, '2010-03-27', 'Go4SC2 ESL March Finals', '680', 'LucifroN', '', 1),
(19, '2010-04-10', 'Go4SC2 ESL Saturday', '135', 'Mardow', '', 0),
(20, '2010-04-03', 'Go4SC2 ESL Saturday', '135', 'White-Ra', '', 5),
(21, '2010-03-20', 'CraftCup EU', '100', 'Morrow', '', 0),
(22, '2010-04-09', 'CraftCup EU', '50', 'HayprO', '', 10),
(23, '2010-04-25', 'Zotac Cup', '135', 'NaNi', '', 0),
(25, '2010-04-18', 'Zotac Cup', '135', 'Zpux', '', 0),
(26, '2010-04-25', 'Zotac Cup', '135', 'NaNi', '', 0),
(27, '2010-04-16', 'Craft Cup EU', '50', 'Dimaga', '', 3),
(29, '2010-04-22', 'Craft Cup US', '50', 'HuK', '', 12),
(30, '2010-04-11', 'Craft Cup US', '50', 'HuK', '', 12),
(31, '2010-04-19', 'Go4SC2 ESL Saturday', '135', 'TheLittleOne', '', 11),
(33, '2010-04-26', 'Go4SC2 ESL Saturday', '135', 'DeMusliM', '', 6),
(34, '2010-04-25', 'GosuCup Invitational 1st', '150', 'HayprO', '', 10),
(35, '2010-04-25', 'GosuCup Invitational 2nd', '50', 'LucifroN', '', 1),
(36, '2010-04-25', 'GosuCup Invitational 3rd', '50', 'Nazgul', '', 9),
(44, '2010-04-10', 'Hello, Goodbye 3rd', '175', 'iNkA', '', 0),
(40, '2010-04-10', 'Hello, Goodbye 2nd', '300', 'Machine', '', 4),
(41, '2010-04-10', 'Hello, Goodbye 4th', '80', 'Ruthless', '', 0),
(42, '2010-03-27', 'TeamLiquid SC2 Invitational 2nd', '150', 'TheLittleOne', '', 11),
(43, '2010-03-27', 'TeamLiquid SC2 Invitational 3rd', '50', 'Nazgul', '', 9),
(45, '2010-04-16', 'Gosucoaching weekly', '100', 'Sheth', '', 0),
(47, '2010-04-16', 'Huk vs Machine Showmatch', '200', 'Machine', '', 4),
(49, '2010-04-21', 'Assem vs White-Ra showmatch', '150', 'White-Ra', '', 5);

Shade692003

Canada702 Posts

April 29 2010 15:47 GMT

#12

oh nevermind, the playerid is the winning player, so it's ok, I thought it was supposed to link every players that played in a tournament, which would have been a 0.n 0.n relationship. Also, you should specify that the pid is a foreign key. (but that won't solve the issue anyway)

What you probably want is a count(tournament.pid)

Edit: maybe something like

select player.nick, count(tournament.pid) as NumberOfWins
from player left join tournament
on tournament.pid = player.pid
group by player.nick
order by count(tournament.pid), player.nick

s.a.y

Croatia3840 Posts

April 29 2010 15:58 GMT

#13

On April 30 2010 00:47 Shade692003 wrote:
oh nevermind, the playerid is the winning player, so it's ok, I thought it was supposed to link every players that played in a tournament, which would have been a 0.n 0.n relationship. Also, you should specify that the pid is a foreign key. (but that won't solve the issue anyway)

What you probably want is a count(tournament.pid)

Edit: maybe something like

select player.nick, count(tournament.pid) as NumberOfWins
from player left join tournament
on tournament.pid = player.pid
group by player.nick
order by count(tournament.pid), player.nick

doesn't help either. something is broken, and i don't know what it is. sum should stay, as i need the sum of player.pid=tournament.pid not count (then it would count the number of tournaments where a player won some money, not the ammount)

s.a.y

Croatia3840 Posts

April 29 2010 15:59 GMT

#14

that second part is very good, but it counts wins, just needs a extra touch to count money not number of appearances.

EDIT: got it to work, replaced count(tournament.pid) with sum(tournament.prize_money)

thank YOU very much for helping me out!

Flaccid

8929 Posts

April 29 2010 16:04 GMT

#15

This works:

SELECT p.pid, p.nick, SUM(t.winnings) AS sumwinnings FROM players AS p INNER JOIN tournaments AS t ON p.pid=t.pid GROUP BY p.pid, p.nick ORDER BY sumwinnings DESC

or

SELECT p.pid, p.nick, SUM(t.winnings) AS sumwinnings FROM players AS p LEFT JOIN tournaments AS t ON p.pid=t.pid GROUP BY p.pid, p.nick ORDER BY sumwinnings DESC

if you want players with null winnings as well.

I just tested it and it should work.

edit: nvm, you already got your answer.

s.a.y

Croatia3840 Posts

April 29 2010 16:09 GMT

#16

On April 30 2010 01:04 Flaccid wrote:
This works:

SELECT p.pid, p.nick, SUM(t.winnings) AS sumwinnings FROM players AS p INNER JOIN tournaments AS t ON p.pid=t.pid GROUP BY p.pid, p.nick ORDER BY sumwinnings DESC

or

SELECT p.pid, p.nick, SUM(t.winnings) AS sumwinnings FROM players AS p LEFT JOIN tournaments AS t ON p.pid=t.pid GROUP BY p.pid, p.nick ORDER BY sumwinnings DESC

if you want players with null winnings as well.

I just tested it and it should work.

edit: nvm, you already got your answer.

yeah thanks, that works also

gogo TL.net

Shade692003

Canada702 Posts

April 29 2010 16:17 GMT

#17

Oh sorry by tournament winnings I was thinking of that ; wins not money :S Silly me yeah sum from the money column obviously is the way to go lol.

Please or register to reply.

[H] Advanced mysql knowledge required

Completed

Ongoing

Upcoming