mysql - How to select records only when the SUM(field) from a separate table > 0 using MAX to identify the most recent 'field' -

i have been battling query on day now. sql not amazing, might explain why! anyway, i'd appreciate if clarify i'm doing wrong here.

create table `business` (   `business_id` int(11) not null auto_increment, )  create table `business_unit` (   `business_unit_id` int(11) not null auto_increment,   `business_id` int(11) not null, )   create table `offer` (   `offer_id` int(11) not null auto_increment,   `business_unit_id` int(11) not null,   `points_required` int(11) not null, )  create table `points_balance` (   `points_balance_id` int(11) not null auto_increment,   `mobile_user_id` int(11) not null,   `business_unit_id` int(11) not null,   `points` int(11) not null default '0',   `record_created` timestamp not null default current_timestamp, ) 

the data be:

insert `business` (`business_id`) values (1); insert `business` (`business_id`) values (2);  insert `business_unit` (`business_unit_id`,`business_id`) values (11, 1); insert `business_unit` (`business_unit_id`,`business_id`) values (12, 1); insert `business_unit` (`business_unit_id`,`business_id`) values (13, 2); insert `business_unit` (`business_unit_id`,`business_id`) values (14, 2);  insert `offer` (`offer_id`,`business_unit_id`,`points_required`) values (21, 11, 50); insert `offer` (`offer_id`,`business_unit_id`,`points_required`) values (22, 12, 50); insert `offer` (`offer_id`,`business_unit_id`,`points_required`) values (23, 12, 60); insert `offer` (`offer_id`,`business_unit_id`,`points_required`) values (24, 13, 100); insert `offer` (`offer_id`,`business_unit_id`,`points_required`) values (25, 14, 30); insert `offer` (`offer_id`,`business_unit_id`,`points_required`) values (26, 14, 150);  insert `points_balance` (`points_balance_id`,`user_id`,`business_unit_id`,`points`,`record_created`) values (31, 27, 11, 10, '2013-04-01'); insert `points_balance` (`points_balance_id`,`user_id`,`business_unit_id`,`points`,`record_created`) values (32, 27, 11, 30, '2013-04-02'); insert `points_balance` (`points_balance_id`,`user_id`,`business_unit_id`,`points`,`record_created`) values (33, 27, 12, 10, '2013-03-02'); insert `points_balance` (`points_balance_id`,`user_id`,`business_unit_id`,`points`,`record_created`) values (34, 27, 12, 20, '2013-03-04'); insert `points_balance` (`points_balance_id`,`user_id`,`business_unit_id`,`points`,`record_created`) values (34, 27, 14, 20, '2013-04-12'); insert `points_balance` (`points_balance_id`,`user_id`,`business_unit_id`,`points`,`record_created`) values (34, 27, 14, 100, '2013-04-14'); 

records in points balance table insert only, user's balance unit on recent record user/unit

business units within same business share points balance (i.e. user's total points sum of points on various units within business).

i want select offers user has enough points redeem them.

select up.user_id, up.points, o.* offer o join (     select user_id, business_id, sum(points) points      points_balance pb     join business_unit bu on pb.business_unit_id = bu.business_unit_id     pb.points_balance_id in (         select max(pb2.points_balance_id)         points_balance pb2         pb2.user_id = 27         group pb2.business_unit_id)     group user_id, business_id ) on up.points >= o.points_required 

the query above returning 1 instance of same offer multiple times (times number of business user has points at) :(

the result set i'm looking is:

user_id | points    | offer_id 27      |   50      |  21 27      |   50      |  22 27      |   100     |  24 27      |   100     |  25 

thanks all. g

here query think want:

select up.user_id, up.business_id, up.allpoints, o.* (select user_id, business_id, sum(points_balance) allpoints       points_balance pb join            business_unit bu            on pb.business_unit_id = bu.business_unit_id       group user_id, business_id      ) join      offers o      on o.point_required <= up.allpoints; 

the logic behind query simpler approach taking. first subquery calculates total number of points available user across business units within business. finds matching points.

you can add where clauses either in subquery or in overall query limit particular businesses or users.

also, based on field names in text of question, not ones in query. different.