Jump to content

get rows who have serveral columns matched with an array


Recommended Posts

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.

drop 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 by Rutger83
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...