Hi, Alejandro,

It's encouraging to see that people don't give up on pure set-based solutions! Your solution is interesting and runs for a minute on my laptop against ~55,000 rows. I've got a few more pure set-based solutions from other readers, and one of them even performs better than all the solutions that I presented in the September column. Stay tuned until the October column, where I present a couple of those solutions. Thanks for sharing your solution.
--Itzik

Itzik Ben-Gan 9/21/2003 3:48:23 PM

I really enjoy every single article on this Web site. Here's another try at solving the relational-division puzzle:

select c.courseid,
case
when coalesce(min(a.c2), c.courseid) > c.courseid then c.courseid
else coalesce(min(a.c2), c.courseid)
end as min_courseid
from courses as c
left join
(
select cm1.courseid,
cm2.courseid,
count(*)
from courseMaterials as cm1 inner join courseMaterials as cm2
on cm1.materialid = cm2.materialid
where cm1.courseid <> cm2.courseid
group by cm1.courseid, cm2.courseid
having 2 * count(*) = (
select count(*)
from courseMaterials as cm
where cm.courseid in (cm1.courseid, cm2.courseid)
)
) as a(c1, c2, mats)
on c.courseid = a.c1
group by c.courseid

I'm not totally proficient in English yet (still learning), but here's my explanation of the solution. Joining the table courseMaterials to itself by materialid and grouping on both course ids (cm1.courseid, cm2.courseid) having its count(*) multiplied by 2 equal to the number of materials used by both course ids will give us the pairs of courses that use the same materials.

If we left-join the table courses to the previous result and group by courseid, we can have the minimum between the courseid and the ones that match.

Regards,
Alejandro Mesa

Alejandro Mesa 9/5/2003 8:04:37 AM