Re: Help needed: Retrieve only rows where numbers are concurrent
Date: Thu, 11 Jul 2002 13:03:02 GMT
Message-ID: <amfX8.8$Jd4.845_at_petpeeve.ziplink.net>
Here's a query that gives you part of what you need:
select
trainer_id,
room_id
from
temporary_tbl
group by
trainer_id,
room_id
having
count (distinct slot_id) = 3;
This gives you the correct trainer_id and room_id pairs, but it doesn't tell you what the slot_ids are.
We could use this query as an inline view in the from clause of a larger
query, in order to allow
the slot_ids to be printed. I'm not sure this is going to be right,
without testing, but here's the
general idea:
select
t.trainer_id,
t.room_id,
t.slot_id
from
temporary_tbl t,
(select
trainer_id,
room_id
from
temporary_tbl
group by
trainer_id,
room_id
having
count (distinct slot_id) = 3) triplet
where
triplet.trainer_id = t.trainer_id
and
triplet.room_id = t.room_id;
I'm sure I've got at least one typo in the above, but I can't find it by desk checking.
--
Regards,
David Cressey
www.dcressey.com
"KrasS" <ballonier_at_mousehut.com> wrote in message
news:31113d1d.0207110105.1bb1e628_at_posting.google.com...
> Hi,
>
> I have the following (temporary) table:
>
> trainer_id room_id slot_id
> ----------- ------- -----------
> 1 1 147
> 1 1 148
> 1 1 149
> 5 1 147
> 5 1 148
> 5 1 149
> 7 1 147
> 7 1 148
> 7 1 149
> 11 1 148
> 1 2 147
> 1 2 148
> 1 2 149
>
> etc...
>
> How should I query this table to retrieve a list where:
> trainer_id and room_id have 3 concurrent slot_id available, like:
>
> trainer_id room_id slot_id
> ----------- ------- -----------
> 1 1 147
> 1 1 148
> 1 1 149
>
>
> Eventually, I will use a variable to define the number of required
> concurrent slots, but I don't think that will change the structure of
> the query anyway.
>
> Thanks for helping me out!
>
> Kind regards,
>
> John Bakta
Received on Thu Jul 11 2002 - 15:03:02 CEST