CODEWITHSUNDEEP
mysqlsqlleft-join
user3804188
user3804188

Reputation: 140

This simple left join operation doesn't work, but why?

I am a bit confused about this simple mysql-query:

SELECT 
    `user`.`id`,
    `user`.`name`
 FROM 
    `#__users` `user`
 LEFT JOIN 
    (SELECT 
        `_s`.`userId`,
        SUM(`_s`.`fild12`) AS `fild12`,
        SUM(`_s`.`fild13`) AS `fild13`
     FROM 
        `#__logbookEventdata` `_s`
     GROUP BY `_s`.`userId`) `stats`
 ON 
    (`stats`.`userId`=`user`.`id`)

It returns this output:

id  | name
----+-------------
291 | bla
289 | Super User
290 | user1

actually I expected that the columns from stats would have been added to the output. But they weren't. When I simply call the stats-SELECT-query I get this output:

 userId | fild12 | fild13   
--------+--------+--------
289     |15      |0
290     |0       |0

I can't figure out why this doesn't work. I hope you guys can help me. Thanks!

Upvotes: 1

Views: 43

Answers (2)

PC-CD Interactive Multimedia
PC-CD Interactive Multimedia

Reputation: 46

You have two set. First is user and second is stats. You multiple first to second. First all rows to second all rows. If you change the set orders than not keep same result, because base rows (after from table) not equal as the joined rows (after left join subselect). The result keep all rows by from table/subquery. I recommend you change transpose the two sets.

Upvotes: 0

Dave Zych
Dave Zych

Reputation: 21887

You have to add the fields from stats to the main select:

SELECT 
    `user`.`id`,
    `user`.`name`,
    `stats`.*
FROM 
`#__users` `user`
LEFT JOIN 
    (SELECT 
        `_s`.`userId`,
        SUM(`_s`.`fild12`) AS `fild12`,
        SUM(`_s`.`fild13`) AS `fild13`
     FROM 
        `#__logbookEventdata` `_s`
     GROUP BY `_s`.`userId`) `stats`
ON 
    (`stats`.`userId`=`user`.`id`)

Upvotes: 3

Related Questions