Guys can someone help me with this mysql problem I have?
I have a table that stores users waiting to play a 1v1 game. I call it mm_matchmaking
It looks like this
mm_id , mm_usr_id, mm_game
Now, I want to pair them by their game, with the lowest mm_id being served first.
So example:
id, user, game
1, 2, battleship
2, 3, battleship
3, 12, battleship
4, 1, battleship
For this I'd want the following result:
user_1 , user_2, game
2, 3, battleship
3, 4, battleship
I'm guessing I have to do some grouping in the first part, and probably a sub query using the results of the first, but I can't work out how to do it exactly
* the results have to look like this:
user_1 , user_2, game
2, 3, battleship
12, 1, battleship
Do your own homework you greasy spic
>>54889586
It's not homework. It's work.
I'm self employed working on my next project
SELECT * FROM mm_matchmaking a, mm_matchmaking b WHERE a.mm_usr_id != b.mm_usr_id and a.mm_game = b.mm_game order by a.mm_id asc, b.mm_id asc
This gives me all the possible pairings ordered by ID, giving the earliest players the first games.
But there's still a lot of lines returned that I don't want, as I only want 1 pairing per user...
>>54890673
So store that query into a new table and get the first two rows from it?
>>54890844
No.. the first 2 rows with 4 players waiting to play (1,2,3,4) look like this:
1,2
1,3
whereas I need 1,2 and 3,4
>>54890508
In that case, I can offer consulting services for $50/hour, one hour minimum.
>>54890921
How do I know you can solve the problem
>>54890947
Ehh fuck it, I'm in a good mood. Keep the thread alive, I'll be at a computer in 10 mins. Not typing SQL on my phone.
>>54891191
Thanks man, I'll post you some table data in a minute
>>54891191-- phpMyAdmin SQL Dump
-- version 4.0.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 03, 2016 at 05:58 PM
-- Server version: 5.5.49-0ubuntu0.14.04.1
-- PHP Version: 5.5.9-1ubuntu4.17
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `project026`
--
-- --------------------------------------------------------
--
-- Table structure for table `wpr_wtprps`
--
CREATE TABLE IF NOT EXISTS `wpr_wtprps` (
`wpr_id` int(11) NOT NULL AUTO_INCREMENT,
`wpr_usr_id` int(11) NOT NULL,
`wpr_amount` int(11) NOT NULL,
PRIMARY KEY (`wpr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `wpr_wtprps`
--
INSERT INTO `wpr_wtprps` (`wpr_id`, `wpr_usr_id`, `wpr_amount`) VALUES
(1, 1, 10),
(2, 2, 10),
(3, 3, 10);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
I have come up with this query which shows each possible pairing once:SELECT *
FROM wpr_wtprps a, wpr_wtprps b
WHERE a.wpr_id < b.wpr_id
AND a.wpr_amount = b.wpr_amount
ORDER BY a.wpr_id ASC , b.wpr_id ASC
LIMIT 0 , 30
>>54891306
bump for safety
>>54891514
Sorry anon it's going to take longer than I thought to get to a computer. I'll post if the thread is still here, otherwise- look into SELECT DISTINCT.
>>54891573
I know about distinct but I haven't found out how it would help me with this as I want distincts over 2 colums while preserving both columns
>>54891306
Is pic related the output you want, or is it important that each value of user_1 show up only once?
>>54891777
each value has to be unique over both columns, so 1,3 and 2,3 would not be included
>>54891951
for 4 users: 1,2,3,4 i would like
1,2
3,4
as outputs
>>54891951
>>54891984
To be honest anon, I think it'd be easier to filter this in the application code. Do something like this to fetch >>54891777 :SELECT
a.wpr_usr_id AS user_1,
b.wpr_usr_id AS user_2,
a.wpr_amount AS amount
FROM
wpr_wtprps a,
wpr_wtprps b
WHERE
a.wpr_usr_id < b.wpr_usr_id
AND
a.wpr_amount = b.wpr_amount
GROUP BY
a.wpr_id, b.wpr_id
ORDER BY
a.wpr_id ASC,
b.wpr_id ASC
Then filter for uniqueness application-side. I can think of a few ways to probably do this in SQL, but they would be rather obtuse and probably no faster than doing it app-side.
>>54892103
Okay, thank you, I will go with this until I figure out a better way (if needed)