mysql - Get Multiple COUNTS Multiple JOINS from Schedule table -

i have 3 tables:

test_member, test_member_addon & test_schedule.

• a member can have 1 test_member.type (test_schedule.prod_type = 1)
• a member can have multiple test_member_addon.type (test_schedule.prod_type = 2)

i trying return distinct counts each distinct test_schedule.mem_id, test_schedule.prod_code in schedule future dated , schedule.status = 1.

schedules should have correct amount of payments eg. 52 'weekly'. if correct & actual not match, can fix.

my query: not return correct values amount, period, correct or actual.

select distinct s.mem_id, case when s.prod_type = 1 round(m.next_amount,2) else round(ma.next_amount,2) end amount,  case when s.prod_type = 1 m.period else ma.period end period, case when s.prod_type = 1  ( case when m.period = 'weekly' 52  when m.period = 'fortnightly' 27 end ) else ( case when ma.period = 'weekly' 52  when ma.period = 'fortnightly' 27 end  ) end correct, count(*) actual,  s.prod_code,  s.prod_type `test_schedule` s, `test_member` m, `test_member_addon` ma  m.id = s.mem_id , ma.mem_id = m.id  , s.status = 1  group s.prod_code, s.mem_id  order s.mem_id, s.prod_type; 

db build scripts:

create table `test_member` (   `id` int(11) not null auto_increment,   `next_amount` float default null,   `period` varchar(50) default null,   `prod_code` int(11) not null,   primary key (`id`) ) engine=innodb auto_increment=4 default charset=latin1;  insert `test_member` (`id`, `next_amount`, `period`, `prod_code`) values ('1','50','weekly',11), ('2','35','fortnightly',11);  create table `test_member_addon` (   `mem_addon_id` int(11) not null auto_increment,   `mem_id` int(11) default null,   `next_amount` float default null,   `period` varchar(50) default null,   `prod_code` int(11) not null,   primary key (`mem_addon_id`) ) engine=innodb auto_increment=6 default charset=latin1;  insert `test_member_addon` (`mem_addon_id`, `mem_id`, `next_amount`, `period`, `prod_code`) values ('1','1','25.55','weekly',22), ('2','1','15','fortnightly',33);  create table `test_schedule` (   `sched_id` int(11) not null auto_increment,   `mem_id` int(11) not null,   `prod_code` int(11) not null,   `prod_type` int(11) not null,   `status` smallint(4) not null,   `amount` float not null,   `sched_date` date not null,   primary key (`sched_id`) ) engine=innodb auto_increment=22 default charset=latin1;  insert `test_schedule` (`sched_id`, `mem_id`, `prod_code`, `prod_type`, `status`, `amount`, `sched_date`) values ('1','1','11','1','1','50','2014-03-21'), ('2','1','11','1','1','50','2014-03-28'), ('3','1','11','1','1','50','2014-04-04'), ('4','1','11','1','1','50','2014-04-11'), ('5','1','11','1','1','50','2014-04-18'), ('6','1','22','2','1','25.55','2014-03-21'), ('7','1','22','2','1','25.55','2014-03-28'), ('8','1','22','2','1','25.55','2014-04-04'), ('9','1','22','2','1','25.55','2014-04-11'), ('10','1','22','2','1','25.55','2014-04-18'), ('11','1','22','2','1','25.55','2014-04-25'), ('12','1','22','2','1','25.55','2014-05-02'), ('13','1','22','2','1','25.55','2014-05-09'), ('14','1','33','2','1','15','2014-03-21'), ('15','1','33','2','1','15','2014-04-04'), ('16','1','33','2','1','15','2014-04-18'), ('17','1','33','2','1','15','2014-05-02'), ('18','1','33','2','1','15','2014-05-16'), ('19','1','33','2','1','15','2014-05-30'), ('20','1','33','2','1','15','2014-06-13'), ('21','1','33','2','1','15','2014-06-27');  current return data: mem_id |    amount   |    period    | correct  |  actual  |prod_code | prod_type 1      |    50.00    |    weekly    |    52    |    10    |    11    |    1 1      |    25.55    |    weekly    |    52    |    16    |    22    |    2 1      |    25.55    |    weekly    |    52    |    16    |    33    |    2   correct data: mem_id |    amount   |    period    | correct  |  actual  |prod_code | prod_type 1      |    50.00    |    weekly    |    52    |    5     |    11    |    1 1      |    25.55    |    weekly    |    52    |    8     |    22    |    2 1      |    15       | fortnightly  |    27    |    8     |    33    |    2  

try way

select mem_id, prod_code, prod_type, period,         round(max(amount), 2) amount,         case when period = 'weekly'  52 else 27 end correct,              count(*) actual   (   select s.*, m.period     test_schedule s join test_member m       on s.mem_id = m.id       , s.prod_code = m.prod_code      , s.prod_type = 1    s.status = 1    union    select s.*, a.period     test_schedule s join test_member_addon        on s.mem_id = a.mem_id       , s.prod_code = a.prod_code      , s.prod_type = 2    s.status = 1  ) q  group mem_id, prod_code, prod_type, period 

output:

 | mem_id | prod_code | prod_type |      period | amount | correct | actual | |--------|-----------|-----------|-------------|--------|---------|--------| |      1 |        11 |         1 |      weekly |     50 |      52 |      5 | |      1 |        22 |         2 |      weekly |  25.55 |      52 |      8 | |      1 |        33 |         2 | fortnightly |     15 |      27 |      8 | 

here sqlfiddle demo