CODEWITHSUNDEEP
mysqlsqldatabase
nais
nais

Reputation: 105

MySQL statment, how to show when a column has not results

I have this two tables

   TABLE_A                       TABLE_B
id_user|name            id_user | points | date
-------------           ------------------------
    1  | A                 1    |   10   |  yesterday
    2  | B                 2    |   10   |  today
    3  | C                 2    |   20   |  today
                           3    |   15   |  today

Well, i need to show how many points will earn EVERY user "today", i need to show it although he doesn't earn any amount of points, something like:

      VIEW_POINTS
name | sum(points)_today
------------------------
  1  |   0
  2  |   30
  3  |   15

I tried differents sentences, but i can't show user 1 when he doesn't earn any points, it just shows the other users that have some points.

Upvotes: 1

Views: 44

Answers (3)

SamiMalik
SamiMalik

Reputation: 160

Hi you can To show 0 (for the value column) if there is no result, use "IFNULL"
select t1.user_id, IFNULL(t2.points, 0) like this.

IFNULL

Upvotes: 1

Harsh
Harsh

Reputation: 1319

You need to use left join:

SELECT a.id_user AS name, SUM(COALESCE(b.points, 0)) AS `sum(points)_today`
FROM TABLE_A a LEFT JOIN TABLE_B b
    ON a.id_user = b.id_user AND b.date = 'today'
GROUP BY a.id_user

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do it using a LEFT JOIN:

SELECT t1.id_user, t1.name, COALESCE(SUM(t2.points), 0) AS points
FROM TABLE_A AS t1 
LEFT JOIN TABLE_B AS t2 ON t1.id_user = t2.id_user AND t2.date = 'today'
GROUP BY t1.id_user, t1.name

The crucial part of the LEFT JOIN is the fact that it's ON clause also contains the t2.date = 'today' predicate: this way all TABLE_A rows are selected, even the ones that have not a today's date.

Demo here

Upvotes: 3

Related Questions