Finding a subset from a resultset
Dear All,
I have a table 'bundlecourse' with two fields ibundleid and icourseid. a bundle contains many courses and a course may belong to many bundles
Now I need to find all such bundles containing exactly the courses 188,189 and 192
when I fire the below query, the results are:
select ibundleid,icourseid from bundlecourse where icourseid in (188,189,192)
order by ibundleid,icourseid
60188
60189
62188
62189
65188
65189
65192
66188
66189
67192
79188
79189
80188
80189
But I need the result as bundleid 65
i.e
65188
65189
65192
Can anybody help me in building the query to get the result?
Thanks in advance
Finny
You want the ibundleid values that are in all of 3 sets; the sets with icoursid values of 188, 189, and 192.
One way of doing that, if your database supports the SQL syntac (not all do) is:
select a.ibundleid
from
(select ibundleid from courses where icourseid = 188) a,
(select ibundleid from courses where icourseid = 189) b,
(select ibundleid from courses where icourseid = 192) c
where a.ibundleid = b.ibundleid
and a.ibundleid = c.ibundleid
Another way of doing that, if you can safely assume that no pair of ibundleid and icourseid are duplicated, is to count the rows that each potential ibundleid is in when restricting rows to eligible icourseid values, e.g.:
select ibundleid
from courses
where icourseid in (188,189,192)
group by ibundleid
having count(*) = 3
An alternate syntax for databases that don't support a having clause is:
select ibundleid
from
(select ibundleid, count(*) the_count
from courses
where icourseid in (188,189,192)
group by ibundleid
)
where the_count = 3
Hi StuDerby,
Thanks a lot.
The second query with having clause was the one which exactly solved my purpose.
Thanks once again.
Regards,
Finny
> You want the ibundleid values that are in all of 3
> sets; the sets with icoursid values of 188, 189, and
> 192.
>
> One way of doing that, if your database supports the
> SQL syntac (not all do) is:
> > select a.ibundleid
> from
> (select ibundleid from courses where icourseid = 188)
> a,
> (select ibundleid from courses where icourseid = 189)
> b,
> (select ibundleid from courses where icourseid = 192)
> c
> where a.ibundleid = b.ibundleid
>and a.ibundleid = c.ibundleid
> code]
>
> Another way of doing that, if you can safely assume
> that no pair of ibundleid and icourseid are
> duplicated, is to count the rows that each potential
> ibundleid is in when restricting rows to eligible
> icourseid values, e.g.:
> [code]
> select ibundleid
> from courses
> where icourseid in (188,189,192)
> group by ibundleid
> having count(*) = 3
>
> An alternate syntax for databases that don't support
> a having clause is:
> > select ibundleid
> from
> (select ibundleid, count(*) the_count
> from courses
> where icourseid in (188,189,192)
> group by ibundleid
> )
> where the_count = 3
>
