php - i want to use left and inner join in 3 tables in mysql? -


these tables. first 1 appusers table.

create table if not exists `appusers` ( `id`  int(11) unsigned not null auto_increment, `email` varchar(50) not null, `is_active` tinyint(2) not null default '0', `zip` varchar(20) not null, `city` text not null, `country` text not null, `created` timestamp not null default current_timestamp on update current_timestamp, primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=23 ; 

second table stickeruses table.

create table if not exists `stickeruses` ( `id` int(11) not null auto_increment, `user_id` int(11) not null, `sticker_id` int(11) not null, `count` int(11) not null, primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=24 ; 

third table devices

create table if not exists `devices` ( `id` int(11) not null auto_increment, `user_id` int(11) not null, `regid` varchar(300) not null, `imei` varchar(50) not null, `device_type` tinyint(2) not null, `notification` tinyint(2) not null default '1', `is_active` tinyint(2) not null default '0', `activationcode` int(6) not null, `created` timestamp not null default current_timestamp, primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=28 ; 

i want find sum(stickeruses.count) , count(devices.id) appusers.

here query.

select `appuser`.`id`, `appuser`.`email`, `appuser`.`country`, `appuser`.`created`,      `appuser`.`is_active`, sum(`stickeruse`.`count`) total, count(`device`.`id`)       tdevice `stickerapp`.`appusers` `appuser` left join `stickerapp`.`stickeruses` `stickeruse`      on (`stickeruse`.`user_id`=`appuser`.`id`) inner join `stickerapp`.`devices` `device`       on (`device`.`user_id`=`appuser`.`id`) `appuser`.`is_active` = 1   group `appuser`.`id` limit 10 

when applying each join separately results right, want combine both joins. , when doing results wrong. please help.

when mixing join , left join idea use parentheses make clear intent is.

i don't know need, these syntaxes might give different results:

from left join ( b join c on b..c.. ) bc on a..bc.. ( left join b on a..b.. ) ab join c on ab..c.. 

also, can rearrange them from join c left join b (plus parentheses) or of several other arrangements. granted, pairs rearrangements equivalent.

also, beware; aggregates (such sum()) inflated values when joining. think of way: first joins appropriate combinations of rows tables, sum adds them up. in mind, see if works better:

select  a.`id`, a.`email`, a.`country`, a.`created`, a.`is_active`,       ( select  sum(`count`)              stickerapp.stickeruses              user_id = a.id        ) total,        ( select  count(*)              stickerapp.devices              user_id = a.id        ) tdevice      stickerapp.`appusers`      a.`is_active` = 1     group  a.`id`     limit  10  

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -