CODEWITHSUNDEEP
mysqlsql
Atm
Atm

Reputation: 183

Pair two types of values in MySQL

I have a table:

Type | Value
1    | '1test1'
2    | '2test1'
2    | '2test2'
2    | '2test3'

I want to get a result containing a pair where each entry from each type is used at least once, but not more than required.

From the example table above, I want the following result:

1test1 - 2test1
1test1 - 2test2
1test1 - 2test3

If the table is:

Type | Value
1    | '1test1'
1    | '1test2'
1    | '1test3'
2    | '2test1'
2    | '2test2'
2    | '2test3'

I want the following result:

1test1 - 2test1
1test2 - 2test2
1test3 - 2test3

If the table is:

Type | Value
1    | '1test1'
1    | '1test2'
2    | '2test1'
2    | '2test2'
2    | '2test3'

I want the following result:

'1test1' - '2test1'
'1test2' - '2test2'
'1test1' - '2test3'
'1test1' - '2test1'
'1test2' - '2test2'
'1test1' - '2test3'

I want each type to be repeated equally as other values in the same type. There shouldn't be a value from type that is repeated more often than other values in the same type.

What is the most elegant way to do it with SQL or stored procedure, or with a series of SQL statements?

Upvotes: 0

Views: 279

Answers (3)

Svip
Svip

Reputation: 3128

It is somewhat easy to do when you have you don't have the same amount of rows for each type, but once you do, it gets somewhat tricky.

So I came up with this:

CREATE PROCEDURE test()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE type1, type2 INT;
  DECLARE value1, value2 VARCHAR(5);
  DECLARE cur1 CURSOR FOR SELECT type,value FROM testtable WHERE Type = 1;
  DECLARE cur1 CURSOR FOR SELECT type,value FROM testtable WHERE Type = 2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  if (SELECT COUNT(Value) FROM testtable WHERE Type = 1)
    = (SELECT COUNT(Value) FROM testtable WHERE Type = 2)
  then
      OPEN cur1;
      OPEN cur2;

      CREATE TEMPORARY TABLE test1 (
        Value1 varchar(12),
        Value2 varchar(12)
      )

      read_loop: LOOP
        FETCH cur1 INTO type1, value1;
        FETCH cur2 INTO type2, value2;

        IF done THEN
          LEAVE read_loop;
        END IF;
        INSERT INTO test1 VALUES(value1, value2);
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;

      SELECT * FROM test1;
      DROP TABLE test1;
  ELSE
    SELECT t1.Value, t2.Value
      FROM testtable t1
        LEFT JOIN testtable t2 ON t2.Type = 2
      WHERE t1.Type = 1
    UNION SELECT t1.Value, t2.Value
      FROM testtable t1
        RIGHT JOIN testtable t2 ON t2.Type = 2
      WHERE t1.Type = 1;
  END IF;
END;

It's hideous, but it works for your three examples. Somewhat.

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Is this what you want

SELECT 
    s.val AS One,
    r.val AS Second
FROM stuff AS s
LEFT OUTER JOIN (SELECT * FROM stuff WHERE idx = 2) AS r ON r.idx <> s.idx
WHERE s.idx = 1

SQL Fiddle Demo

OUTPUT :

One     |   Second
--------------------
1val1   |   2val1
1val1   |   2val2
1val1   |   2val3
1val2   |   2val1
1val2   |   2val2
1val2   |   2val3

Upvotes: 1

ethrbunny
ethrbunny

Reputation: 10469

This is a somewhat contrived answer but I guess it fits the question:

create table stuff( idx tinyint unsigned, val varchar(50));
insert into stuff( idx, val ) values ( 1, '1val1'), (1, '1val2'), (2,'2val1'),    
   (2,'2val2'), (2, '2val3');


SELECT s0.val v0, s1.val v1 FROM stuff s0 
  JOIN stuff s1 ON s0.idx != s1.idx 
  where s0.idx = 1;

Here's a fiddle.

Upvotes: 1

Related Questions