Determining Connections between data in a single table
- by user1689749
Hi I'm a BA / programmer type doing data analysis on a legacy system. I've been teaching myself SQL to help, but I've appeared to hit upon a problem bigger than my abilities.
I have two tables (generalized for simplicity):
Table Objects
Object_PK
Table Components
Component_PK
Object_FK
Component_Type
There are 100+ distinct values in Component_Type_Code. Given that any object can have N number of Components, how can I see which Component_Type(s) appear with other Component_Type(s)?
For example, the following query tells me what component_types appear with the component_type 'Component_type_1':
select component_type_code, count(*)
from components
where object_fk in (
select object_fk
from components
where component_type_code = 'component_type_1'
)
group by component_type_code
I'd like to get a query to show me all connections
My apologies for the formatting. Any help is appreciated. I've looked at cube and rollup, but didn't know how to apply to this situtation.
