MySQL table concatenation - sort of! 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
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&gt; DROP DATABASE IF EXISTS johnc;
Query OK, 1 row affected (0.00 sec)

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

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

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


TABLE CONTENTS

mysql&gt; 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&gt;


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&gt; SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql&gt; SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
    -&gt; IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -&gt; FROM
    -&gt; (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
    -&gt; LEFT JOIN
    -&gt; (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
    -&gt; 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&gt;


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 &lt;= 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&gt; SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

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

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


EXECUTED WITH Newark/Boston

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

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

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


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.