2

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

1 Answer 1

0

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.

2
  • This works.... However, if the teams are switched I do not get the reverse (ie: | | | | 3 | Carl | 105 ) - where the first 'side' is blank...
    – John C
    Jun 23, 2016 at 21:08
  • Think about a solution with UNION ALL with different sets of columns and GROUP BY with COALESCE to put them back together.
    – Rick James
    Jul 3, 2016 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.