CODEWITHSUNDEEP
mysqlsqlleft-join
veote
veote

Reputation: 1480

MySQL left join does not work correctly

I want to left join 2 tables. So all attributes of left table should be shown in result table, no matter if it can join with other table or not.

When I do the follwing, I dont get the expected result

week_table:
 date       kw note
 ---------- -- ----
 2012-04-01  0 NULL
 2012-04-02  0 NULL

fact_table:
id number_of_application number_of_cluster number_of_jvm number_of_node number_of_was fk_wasversion fk_date    fk_domain fk_osname fk_osarch fk_osversion fk_stage
 -- --------------------- ----------------- ------------- -------------- ------------- ------------- ---------- --------- --------- --------- ------------ --------
  1                   114                 8            80             18            18 6.0           2012-04-01 domain1   Linux     sparc     2            stage1
  2                   114                 8            80             18            18 6.0           2012-04-02 domain1   Linux     sparc     2            stage1
  3                   114                 8            80             18            18 6.0           2012-04-01 domain1   AIX       sparc     2            stage1
  4                   114                 8            80             18            18 6.0           2012-04-02 domain1   Solaris   sparc     2            stage1

When I do this:

select
  w.date,
  coalesce(sum(f.number_of_was), 0)
from 
  week_table w
    left join fact_table f on (w.date = f.fk_date) 
where f.fk_osname = "AIX" 
group by w.date;

I only get : 

 date       coalesce(sum(f.number_of_was), 0)
 ---------- ---------------------------------
 2012-04-01                                18

Expected:

date       coalesce(sum(f.number_of_was), 0)
---------- --------------------------------
2012-04-02                         18

Does someone know why?

Best Regards

Upvotes: 2

Views: 2190

Answers (3)

Diego
Diego

Reputation: 36126

because of where f.fk_osname = "AIX"

if the data doesnt exist on your right table, it is NULL, and you are asking to retrieve only the rows where fk_osname is = "AIX"`

you can do WHERE (f.fk_osname = "AIX" OR f.fk_osname IS NULL)

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65527

Move the criteria on the outer joined table from the WHERE clause to the ON clause:

select
  w.date,
  coalesce(sum(f.number_of_was), 0)
from 
  week_table w
    left join fact_table f on (w.date = f.fk_date and f.fk_osname = "AIX") 
group by w.date;

Upvotes: 11

Virus
Virus

Reputation: 3415

where f.fk_osname = "AIX"

I guess there is only one record with this osname?

Upvotes: 0

Related Questions