Link to home
Start Free TrialLog in
Avatar of Allen Pitts
Allen PittsFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Allen Pitts

ASKER

The GROUP BY query list Components that are listed more that once. And that works great.
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"
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
 )
Thanks Slightwv and johnsome
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.
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; 

Open in new window

Only hits the table once.