sql - Query to get intersect -
i solving problems spj database.
s(snum, sname) [supplier] p(pnum, pname) [part] j(jnum, jname) [project] spj(snum, pnum, jnum, q)
the query is: pairs of projects have @ least 100 parts in common.
have written code far:
select x.jnum, y.jnum spj x, spj y 100 <= (select count(pnum) (select pnum spj spj.jn=x.jn intersect select pnum spj spj.jn=y.jn));
i think solution correct. there other way solve problem?
simply same-part combinations, see how many matches per project pair:
select spj1.jnum jnum1, spj2.jnum jnum2 spj spj1 join spj spj2 on spj1.pnum = spj2.pnum , spj1.jnum < spj2.jnum group spj1.jnum, spj2.jnum having count(distinct spj1.pnum) >= 100;
Comments
Post a Comment