CODEWITHSUNDEEP
mysqlselect
SamYan
SamYan

Reputation: 1571

Obtain all the records in table A, if they meet the conditions with table B

I want to obtain last update (max(bonus_records.id)) of the each bonus (bonus_records.uid - unique id) then meet this conditions:

  1. that have never been redeemed by indicated player
  2. that have been redemed by indicated player but then the redemption count is less than redeem_count exceeded
  3. The bonus redeemed and active at the said moment (is that bonus that has the fields completed and canceled at 0)

Additional information: If redeem_count is equal to 0, then there is no redemption limit for the said bonus

This is the database basic structure:

enter image description here

DB-FIDDLE

DB-FIDDLE v2 (With @Solarflare query)

DB-FIDDLE v3

My query that failing:

SELECT MAX(br1.id) /* Get last update of bonus */,
    br1.uid,
    br1.name, 
    rb1.instance_id,
    rb1.player_id,
    br1.redeem_count,
    rb1.executed,
    rb1.completed,
    rb1.canceled
FROM 
    bonus_records br1
LEFT JOIN
    redeemed_bonuses rb1 ON 
    rb1.bonus_id = br1.id
WHERE 
    (rb1.player_id IS NULL) OR /* never redeemed */
    (rb1.player_id = 1 AND /* if redeemed then */
        (
            br1.redeem_count > ( /* total count of X redemed bonus is less then redeem_count but redeem_count <> 0 */
                SELECT COUNT(*)
                FROM redeemed_bonuses rb2
                INNER JOIN bonus_records br2 ON rb2.bonus_id = br2.id
                WHERE br2.uid = br1.uid AND rb2.completed = 0 AND rb2.canceled = 0
            ) OR
            br1.redeem_count = 0 /* redeem_count = 0 means that there is no established limit of redeem  */
        )
    )
GROUP BY 
    br1.uid

Expected result:

If i have this bonus list:

enter image description here

And this redeemed bonus list:

enter image description here

Then the expected outcome bonus list for player_id = 1 will be:

enter image description here

Upvotes: 2

Views: 129

Answers (1)

Solarflare
Solarflare

Reputation: 11096

There are some problems in your query/logic:

  • select max(id), name, ... group by uid will not give you the row with the maximum id. It will give you the maximum id, and the values of any row that is in that group. If there is only one row per group (e.g. if uid is unique/the primary key), that might be the one you are looking for, otherwise it is not determined (and will fail for MySQL 5.7), see MySQL Handling of GROUP BY and any question on stackoverflow about an errormessage with sql_mode=only_full_group_by.

  • left join ... ON bonus_id = id where rb1.player_id IS NULL will be false if there is any player that has redeemed this bonusid. If you included the playerid in the on-condition, it would be true if the player would not have redeemed all different ids for a given uid (which is probably impossible).

  • something similar happens since you join via rb1.bonus_id = br1.id and apply your condition to this id (but not uid): if there is some old entry with a bigger redeem_count, it evaluates to true even if there is a latest id with a lower redeem_count (that won't be part of the group by, since you filtered it out).

  • instead, you probably would need to apply your filter after left join, e.g using group by ... having ... or select ... from (select ... group by ...) where ...

With this said, I won't fix your query (although it may be salvageable), but write you a new one with a new structure.

Breaking it into steps, first, get a list of all active bonuses:

select * from bonus_records br
where not exists 
  (select 1 from bonus_records br1
   where br1.uid = br.uid and br1.id > br.id);

Next step is to check how often a specific uid has been redeemed by a specific player (the uid-information is obtained by checking the bonus_records-table): 

select br.uid, count(*) 
from redeemed_bonuses rb
join bonus_records br on br.id = rb.bonus_id
where rb.player_id = 1
   and not (rb.completed = 0 and rb.canceled = 0) 
group by br.uid;

The condition not (rb.completed = 0 and rb.canceled = 0) is adepted to fit the requirements according to the comments.

Now join those two and apply your conditions about the actual count being lower than redeem_count:

select pb.*, rd.actual_count from 
  (select * from bonus_records br
   where not exists 
     (select 1 from bonus_records br1
      where br1.uid = br.uid and br1.id > br.id)
  ) pb -- active potential bonuses
left join  
  (select br.uid, count(*) as actual_count 
   from redeemed_bonuses rb
   join bonus_records br on br.id = rb.bonus_id
   where rb.player_id = 1
      and not (rb.completed = 0 and rb.canceled = 0) 
   group by br.uid
  ) rd -- redeemed bonuses by that user
on pb.uid = rd.uid 
where rd.actual_count is null            -- uid never redeemed (left join empty)
   or rd.actual_count < pb.redeem_count  -- still some remaining
   or pb.redeem_count = 0                -- unlimited bonus

Upvotes: 1

Related Questions