Allen Pitts
asked on
PL/SQL More than one element associated with another element
Hello experts,
In the database there are Protocols made up of Components each with it own table and primary key
(Protocol_ID and Component_ID). There is a junction table, Protocol_Component,
that has the Protocol_ID and Component_ID and its own Protocol_Component_ID primary key.
I think this may be fairly simple but I can't figure this out.
How do I make a list of all the Components that are in more than one Protocol ?
Thanks
Allen
In the database there are Protocols made up of Components each with it own table and primary key
(Protocol_ID and Component_ID). There is a junction table, Protocol_Component,
that has the Protocol_ID and Component_ID and its own Protocol_Component_ID primary key.
I think this may be fairly simple but I can't figure this out.
How do I make a list of all the Components that are in more than one Protocol ?
Thanks
Allen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works
Select
protocol_id from
CCM_Protocol_Component
where component_id in
(
select component_id
from CCM_Protocol_Component
group by component_id
having count(*)>1
)
Select
protocol_id from
CCM_Protocol_Component
where component_id in
(
select component_id
from CCM_Protocol_Component
group by component_id
having count(*)>1
)
ASKER
Thanks Slightwv and johnsome
There shouldn't be a reason to hit the table twice.
If you can post a little sample data and expected results, we can likely fix what wasn't working in the original queries.
If you can post a little sample data and expected results, we can likely fix what wasn't working in the original queries.
Not sure I fully understand the requirement, but if I do, this would be better:
SELECT protocol_id
FROM (SELECT protocol_id,
Count(1)
over (
PARTITION BY component_id) cnt
FROM ccm_protocol_component)
WHERE cnt > 1;
Only hits the table once.
ASKER
Perhaps the question was not stated clearly. The Protocol_IDs of Components listed more than once is sought.
Tried
Select
protocol_id from
CCM_Protocol_Component
where component_id in
(
select component_id, count(*)
from CCM_Protocol_Component
group by component_id
having count(*)>1
)
but this returns
"too many values"