CODEWITHSUNDEEP
mysql
lubo
lubo

Reputation: 19

looking for fast query

users table 

    id_name     |name
    1           |lubo
    2           |jano
    3           |elena

here user / score table

id_score    |id_name      |score      |month
1           |1            |0          |1
2           |2            |9          |1
3           |3            |0          |1
4           |1            |2          |2
5           |2            |5          |2
6           |3            |0          |2
7           |1            |0          |3
8           |2            |6          |3
9           |3            |0          |3

i want list of names with months where score is 0. List should looks like this.

name        |months      
lubo        |1,3
jano        |
elena       |1,2,3

can i do this? i have about 1000 rows.

Upvotes: 1

Views: 89

Answers (3)

Mark Byers
Mark Byers

Reputation: 838336

The important steps are:

  • WHERE score = 0 to select only the rows you are interested in.
  • GROUP BY to group per id_name.
  • JOIN to get the name.
  • CONVERT to convert the integer values to a string.
  • GROUP_CONCAT to concatenate the result in each group.

Try this:

SELECT
    table1.name,
    GROUP_CONCAT(CONVERT(month, CHAR(8)) ORDER BY month) AS months
FROM table2
JOIN table1 ON table2.id_name = table1.id_name
WHERE score = 0
GROUP BY table2.id_name

Note that many uses of GROUP_CONCAT are design errors. It is usually better to return one value per cell.

Update: to get all the names use an OUTER JOIN:

SELECT
    table1.name,
    GROUP_CONCAT(CONVERT(month, CHAR(8)) ORDER BY month) AS months
FROM table1
LEFT JOIN table2 ON table2.id_name = table1.id_name AND score = 0
GROUP BY table2.id_name

Upvotes: 3

Haim Evgi
Haim Evgi

Reputation: 125496

use group_concat() like

SELECT name ,
       GROUP_CONCAT(DISTINCT month ORDER BY month SEPARATOR ",") AS months
FROM   users u LEFT JOIN scores s ON u.id_name = s.id_name     
       WHERE score = 0
GROUP BY name;

;

Upvotes: 0

Petar Minchev
Petar Minchev

Reputation: 47373

SELECT N.name, S.month
  FROM scores S
  JOIN names N
    ON S.id_name = N.id_name
 WHERE S.score = 0

Then in your code you can combine the months by names as you wish.

Upvotes: 2

Related Questions