I have table1 like

and users table(users will repeat) like below

how to write a query to get the users satisfying table 1 combination {1,2} and {1,3}, so as per the above data userid 10 and 13 should be the output because user should have combination of {1,2} OR {1,3}

Thanks

CodePudding user response：

I would first count the number or reasons per funid in table1 , and then compare that with the count (distinct reasonid) obtained with a join between table1 and users grouped by userid and funid. If the counts are equal, then that user satisfies the condition.

Something like:

SELECT B.userid, B.funid
FROM (
    SELECT funid, reasonCount = COUNT(*)
    FROM table1
    GROUP BY funid
) A
JOIN (
    SELECT U.userid, T.funid, reasonCount = COUNT(DISTINCT U.reasonid)
    FROM users U
    JOIN table1 T ON T.reasonid = U.reasonid
    GROUP BY U.userid, T.funid
) B ON B.funid = A.funId AND B.reasonCount = A.reasonCount
ORDER BY B.userid, B.funid

The COUNT(DISTINCT ...) is only needed if it is possible that a user could reference the same reason more than once. You can drop the funid and add distinct to the final result to eliminate dups caused by users who satisfy more than one rule set (such as a user referencing reasons 1, 2, and 3).

Results:

See this db<>fiddle for a demo.

CodePudding user response：

(int funid, int reasonid)[] t1 = new[] { (1, 1), (1, 2), (2, 1), (2, 3) };
(int userid, int reasonid)[] t2 = new[] { (10, 1), (10, 2), (11, 1), (12, 3), (13, 1), (13, 3) };
var rules = from x in t1 group x by x.funid into g 
            select (rule: g.Key, reasons: g.Select(x => x.reasonid));
var users = from x in t2 group x by x.userid into g 
            select (user: g.Key, reasons: g.Select(x => x.reasonid));
var result = from r in rules from u in users 
             where r.reasons.SequenceEqual(u.reasons) 
             select (r.rule, u.user);

result is { (1, 10), (2, 13) }