Rutger83 Posted March 24, 2012 Author Share Posted March 24, 2012 (edited) One of the things I am struggeling with at this moment is how to make step 1, giving aswell the bags of candy which dont have any missing candies with the compared candy combination (see updated last post), without using an union at the moment I have this, and it seems to do most of the tricks, besides blacklisted values not working as they should be. expandcollapse popupdrop table if exists "temp"; -- restore candy status update candies set selected = 0, blacklisted = 0; -- set status for some candies update candies set selected = 1 where name in ('candy01','candy02','candy03','candy04'); update candies set blacklisted = 1 where name in ('candy08'); -- select the remaining groups of candy create table "temp" as select groupId, GroupType, max, count(*) as remainingNum, group_concat(name,', ') as remaining from groups natural join members natural join candies where not selected and not blacklisted group by groupid having count(*) <= 4 UNION -- Union with groups which dont have any remaining candies select groupid, GroupType, max, 0 as remainingNum, "" as remaining from groups natural join members natural join candies where selected and not blacklisted group by groupid having count(*) =groups.size; -- making the combinations for: 1 candy remaining, 2 or more candies remaining and 0 candies remaining select t1.groupId as GroupID, t1.GroupType as Gtype, t1.remainingNum as RemaingNum, REPLACE(REPLACE(t1.remaining,t1.name,''),t2.name,'') as remaining, t1.name "Name #1", t2.name "Name #2", t1.max as max from ("temp" natural join members natural join candies) t1 join (candies) t2 on t1.CandyID != t2.CandyID and not t1.selected and not t1.blacklisted and not t2.selected and not t2.blacklisted WHERE t1.remainingNum=1 UNION select t1.groupId as GroupID, t1.GroupType as Gtype, t1.remainingNum as RemaingNum, REPLACE(REPLACE(t1.remaining,t1.name,''),t2.name,'') as remaining, t1.name "Name #1", t2.name "Name #2", t1.max as max from ("temp" natural join members natural join candies) t1 join ("temp" natural join members natural join candies) t2 on t1.groupid=t2.groupid and t1.CandyID < t2.CandyID and t1.CandyID != t2.CandyID and not t1.selected and not t1.blacklisted and not t2.selected and not t2.blacklisted WHERE t1.remainingNum>1 UNION select t1.groupId as GroupID, t1.GroupType as Gtype, t1.remainingNum as RemaingNum, "" as remaining, candies.name "Name #1", candies2.name "Name #2", t1.max as max from temp as t1 LEFT JOIN candies join candies as candies2 on candies.CandyID != candies2.CandyID and not candies.selected and not candies.blacklisted and not candies2.selected and not candies2.blacklisted where remainingNum=0 GROUP BY "Name #1","Name #2" ORDER BY remainingNum ASC, Gtype ASC, max DESC Edited March 24, 2012 by Rutger83 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now