1

Good day!

Let's say I have two tables defined as follows:

TABLE USER(id, name, ...other fields that are unimportant)

| id | name |
-------------
|  1 | John | 
|  2 | Mary | 
|  3 | Luke |
|  4 | Lisa |

TABLE CHOICE(id, uid, kname) where uid is the foreign key to table USER.

Please note: I do NOT know how many distinct kname entries the table CHOICE has. The only thing I know is that they will be limited (say between 0 and 10).

| id | uid |  kname |
---------------------
|  1 |  1  |  city1 |
|  2 |  1  |  city2 |
|  4 |  2  |  city2 |
|  5 |  2  |  city3 |
|  6 |  4  |  city4 |

Is it possible to write a query which returns this table:

| id | name | city1 | city2 | city3 | city4 |
---------------------------------------------
|  1 | John |   1   |   1   |   0   |   0   |
|  2 | Mary |   0   |   1   |   1   |   0   |
|  3 | Luke |   0   |   0   |   0   |   0   |
|  4 | Lisa |   0   |   0   |   0   |   1   |

i. e. the table USER extended with as many additional columns as distinct kname in table CHOICE, labelled with kname and containing a 1 if there exist a row in CHOICE with uid equals to the user id and a zero otherwise.

Matt
  • 14,906
  • 27
  • 99
  • 149
Fabbio
  • 343
  • 2
  • 16
  • It's a pity that you have no presentation layer (e.g. PHP) – Strawberry Nov 25 '14 at 15:18
  • Well, that's exactly the purpose of the question, knowing if it is possible with SQL only :) – Fabbio Nov 25 '14 at 15:21
  • It's possible, but it's so much simpler and more flexible to do in application-level code – Strawberry Nov 25 '14 at 15:25
  • Well, if it's possible you can maybe post some hint about how to do it :) – Fabbio Nov 26 '14 at 12:34
  • OK, look for pivot table queries used in conjunction with prepared statements and (possibly) stored procedures (like this: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) – Strawberry Nov 26 '14 at 12:51

2 Answers2

0

Something like

SELECT u.id, u.name, (SELECT COUNT(c.uid) FROM CHOICE WHERE c.kname = 'city1') AS city1, (SELECT COUNT(c.uid) FROM CHOICE WHERE c.kname = 'city2') AS city2, (SELECT COUNT(c.uid) FROM CHOICE WHERE c.kname = 'city3') AS city4, (SELECT COUNT(c.uid) FROM CHOICE WHERE c.kname = 'city4') AS city4
FROM USER u 
INNER JOIN CHOICE c ON u.id = c.uid
GROUP BY u.id, u.name
Matt
  • 14,906
  • 27
  • 99
  • 149
  • Thanks for the reply! If I get it right, you assume I already know how many distinct entries table CHOICE has. But this is not the case. There could be 0 to 10 different city names that makes it so difficult. That's why I am not even sure it is possible to use a query. – Fabbio Nov 25 '14 at 15:14
  • 1
    Yeh it assumes it is the values you stated in the question, how many city names will there be? It would have been better to give city its own table and assign it ID's then it could have been done no matter how many – Matt Nov 25 '14 at 15:40
0

No need for subqueries. Usually you would just do 

sum(kname = 'city1') as city1

, because the kname = 'city1' equals to true or false, 1 or 0. But you have to deal with possible null values, since you have to left join to get all entries from the users table, even the ones that do not have a corresponding entry in table choice. Therefor the use of the coalesce() function (it returns the first of its parameters, that isn't null).

select 
u.id, 
u.name, 
sum(coalesce(kname, '') = 'city1') as city1, 
sum(coalesce(kname, '') = 'city2') as city2, 
sum(coalesce(kname, '') = 'city3') as city3, 
sum(coalesce(kname, '') = 'city4') as city4 
from user u 
left join choice c on c.uid = u.id 
group by u.id, u.name;

+------+------+-------+-------+-------+-------+
| id   | name | city1 | city2 | city3 | city4 |
+------+------+-------+-------+-------+-------+
|    1 | John |     1 |     1 |     0 |     0 |
|    2 | Mary |     0 |     1 |     1 |     0 |
|    3 | Luke |     0 |     0 |     0 |     0 |
|    4 | Lisa |     0 |     0 |     0 |     1 |
+------+------+-------+-------+-------+-------+
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Thanks for the reply! If I get it right, you assume I already know how many distinct entries table CHOICE has. But this is not the case. There could be 0 to 10 different city names that makes it so difficult. – Fabbio Nov 25 '14 at 15:13
  • Hello! What if I want to read the city3 value (assuming it can have other values than 0 and 1)? Is it still possible? – Fabbio Nov 28 '14 at 07:55
  • What do you mean? It can have whatever value. 0 or 1 are the result of the boolean expression `column_name = 'value'`. – fancyPants Nov 28 '14 at 07:58