Guys can someone help me with this mysql problem I have? I have

* 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

File: Screen Shot 2016-06-03 at 17.42.31.png (7KB, 165x102px) Image search: [Google]

7KB, 165x102px

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