Guidance on complex query

Question

I'm having trouble writing this query. I've figured out how to achieve this with multiple queries but am wondering if it's possible to do it in a single query.

• My goal is to order the results by range.
• The left two columns pertain to group_id = 1, where as the right 2 columns pertain to group_id = 2
• if range1 is positive, range2 should be the same number but negative
• if there are two rows with the same group 1, and range they should not be grouped. (see range2 value: -40)

Dataset:

table: group_data

group_id   | range  | user_value
1          | 5      | 26  
1          | -15    | 29  
1          | 15     | 30  
2          | -40    | 12  
2          | -15    | 20  
2          | -5     | 15  
2          | -40    | 10

My expected Result

range1 | user_value1 | user_value2 | range2
-15    | 29          |             | 15
5      | 26          | 15          | -5
15     | 30          | 20          | -15
40     |             | 12          | -40
40     |             | 10          | -40

This is what I've come up with so far.

SELECT A.range1, A.user_value1, B.user_value2, B.range2 FROM
(
SELECT
    @row_number1:=@row_number1+1 AS RowNumber1,
    g.range as range1,
    g.user_value as user_value1
FROM 
    group_data g
(SELECT @row_number1:=0)AS x 
WHERE 
    g.group_id = 1 AND
    g.range = '-15'
ORDER BY
    g.range DESC
) as A
LEFT JOIN
(
SELECT 
    @row_number2:=@row_number2+1 AS RowNumber2,
    g.user_value as user_value2,
    g.range as range2
FROM 
    group_data g
(SELECT @row_number2:=0)AS y
WHERE 
    g.group_id = 2 AND
    g.range = '15'
ORDER BY
    g.range DESC
) as B
ON A.RowNumber1=B.RowNumber2

This returns:

range1 | user_value1 | user_value2 | range2
-15    | 29          | (null)      | (null)

With -15 and 15 switched to 5 and -5, it returns:

range1 | user_value1 | user_value2 | range2
5      | 26          | 15          | -5

Answer 1

Based on the data and expected results you have provided, I think this query will do what you want. It uses a UNION to simulate a FULL OUTER JOIN (which MySQL doesn't support) on group_data to itself, where the left side has group_id=1 and the right group_id=2.

(SELECT r1.range AS range1, r1.user_value AS user_value1,
       r2.user_value AS user_value2, COALESCE(r2.range, -r1.range) AS range2
FROM       
    (SELECT *
     FROM group_data
     WHERE group_id = 1) r1
LEFT JOIN
    (SELECT *
     FROM group_data
     WHERE group_id = 2) r2
ON r1.range = -r2.range)
UNION
(SELECT COALESCE(r1.range, -r2.range) AS range1, r1.user_value AS user_value1,
       r2.user_value AS user_value2, r2.range AS range2
FROM       
    (SELECT *
     FROM group_data
     WHERE group_id = 1) r1
RIGHT JOIN
    (SELECT *
     FROM group_data
     WHERE group_id = 2) r2
ON r1.range = -r2.range)
ORDER BY range1

Output:

range1  user_value1     user_value2     range2
-15     29              (null)          15
5       26              15              -5
15      30              20              -15
40      (null)          12              -40
40      (null)          10              -40

SQLFiddle