MySQL table concatenation – sort of!

MySQL

I have a db with the following: Name, Team, Score, Points I want to be able to do a query that returns Name, Score, Points for a particular team and another table that returns Points, Score, Name from another team… Then I want to combine them as follows: Name, Score, Points, Points, Name, Score

EXAMPLE

John, Boston, 127, 6
Mark, Boston, 120, 5
Paul, Newark, 110, 4
Carl, Boston, 105, 3
Matt, Newark, 101, 2

Would result in:

John | 127 | 6 | 4 | Paul | 110 |  
Mark | 120 | 5 | 2 | Matt | 101 |  
Carl | 105 | 3 |   |      |     |

OR (depending on which city is selected first)

Would result in:

Paul | 110 | 4 | 6 | John | 127 |  
Matt | 101 | 2 | 5 | Mark | 120 |  
     |     |   | 3 | Carl | 105 |

Thanks

Best Answer

YOUR SAMPLE DATA

DROP DATABASE IF EXIST johnc;
CREATE DATABASE johnc;
USE johnc
CREATE TABLE scores
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    points INT NOT NULL,
    PRIMARY KEY (id),
    KEY (city)
);
INSERT INTO scores (name,city,score,points) VALUES
('John', 'Boston', 127, 6),
('Mark', 'Boston', 120, 5),
('Paul', 'Newark', 110, 4),
('Carl', 'Boston', 105, 3),
('Matt', 'Newark', 101, 2);
select * from scores;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS johnc;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE johnc;
Query OK, 1 row affected (0.01 sec)

mysql> USE johnc
Database changed
mysql> CREATE TABLE scores
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     city VARCHAR(20) NOT NULL,
    ->     score INT NOT NULL,
    ->     points INT NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY (city)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO scores (name,city,score,points) VALUES
    -> ('John', 'Boston', 127, 6),
    -> ('Mark', 'Boston', 120, 5),
    -> ('Paul', 'Newark', 110, 4),
    -> ('Carl', 'Boston', 105, 3),
    -> ('Matt', 'Newark', 101, 2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

TABLE CONTENTS

mysql> select * from scores;
+----+------+--------+-------+--------+
| id | name | city   | score | points |
+----+------+--------+-------+--------+
|  1 | John | Boston |   127 |      6 |
|  2 | Mark | Boston |   120 |      5 |
|  3 | Paul | Newark |   110 |      4 |
|  4 | Carl | Boston |   105 |      3 |
|  5 | Matt | Newark |   101 |      2 |
+----+------+--------+-------+--------+
5 rows in set (0.00 sec)

mysql>

PROPOSED QUERY

SET @x1=0;
SET @x2=0;
SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
FROM
(SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
LEFT JOIN
(SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
USING (k);

PROPOSED QUERY EXECUTED

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM
    -> (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
    -> LEFT JOIN
    -> (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
    -> USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| John | 127 | 6 | 4 | Paul | 110 |
| Mark | 120 | 5 | 2 | Matt | 101 |
| Carl | 105 | 3 |  |  |          |
+---------------------------------+
3 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2016-06-23 18:08 EDT

PROPOSED QUERY #2

SET @x1=0;
SET @x2=0;
SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
(SELECT COUNT(1) x FROM scores WHERE city='Boston' UNION
SELECT COUNT(1) FROM scores WHERE city='Newark') AAA) AA JOIN
(SELECT h*100+t*10+u+1 k FROM
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
WHERE k <= maxnum) k
LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A USING (k)
LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B USING (k);

EXECUTED WITH `Boston/Newark`

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
    -> (SELECT COUNT(1) x FROM scores WHERE city='Boston' UNION
    -> SELECT COUNT(1) FROM scores WHERE city='Newark') AAA) AA JOIN
    -> (SELECT h*100+t*10+u+1 k FROM
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
    -> WHERE k <= maxnum) k
    -> LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A USING (k)
    -> LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| John | 127 | 6 | 4 | Paul | 110 |
| Mark | 120 | 5 | 2 | Matt | 101 |
| Carl | 105 | 3 |  |  |          |
+---------------------------------+
3 rows in set (0.01 sec)

mysql>

EXECUTED WITH Newark/Boston

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
    -> (SELECT COUNT(1) x FROM scores WHERE city='Newark' UNION
    -> SELECT COUNT(1) FROM scores WHERE city='Boston') AAA) AA JOIN
    -> (SELECT h*100+t*10+u+1 k FROM
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
    -> WHERE k <= maxnum) k
    -> LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Newark' ORDER BY id) A USING (k)
    -> LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Boston' ORDER BY id) B USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| Paul | 110 | 4 | 6 | John | 127 |
| Matt | 101 | 2 | 5 | Mark | 120 |
|  |  |  | 3 | Carl | 105         |
+---------------------------------+
3 rows in set (0.00 sec)

mysql>

SECRET SAUCE OF QUERY #2

The subquery k generated a numbered sequence 1..N where N is the maximum number distinct city entries. Since Boston appear three times and Newark only 2 times, subquery k yields the sequence 1..3.

I then LEFT JOIN that sequence to both subquery A (Boston) and subquery B (Newark). As shown, reversing the city names reverses the output.