# 100CodeExamples - Primary/Secondary Lookup

• Script Name 100CodeExamples - Primary/Secondary Lookup
• Visibility Unlisted - anyone with the share link can access
• Description How to do a lookup of primary/secondary groups when only the ID of an entry is known? https://cleandatabase.wordpress.com/2018/12/05/100codeexamples-a-challenge-to-learn-and-teach-primary-replica-lookup/
• Area SQL General / SQL Query
• Contributor Pesse
• Created Tuesday December 04, 2018
• Statement 1
``````create table deathstar_power_nodes (
id integer not null,
label varchar2(100),
primary_node_fk integer,
constraint deathstar_power_nodes_pk primary key (id),
constraint deathstar_power_node_fk_prim foreign key ( primary_node_fk )
references deathstar_power_nodes( id )
)``````
Table created.
• Statement 2
``delete from deathstar_power_nodes``
0 row(s) deleted.
• Statement 3
``````insert all
into deathstar_power_nodes( id, primary_node_fk, label ) values ( 1, null, 'Primary 1' )
into deathstar_power_nodes( id, primary_node_fk, label ) values ( 2, 1, 'Secondary 1.1' )
into deathstar_power_nodes( id, primary_node_fk, label ) values ( 3, 1, 'Secondary 1.2' )
into deathstar_power_nodes( id, primary_node_fk, label ) values ( 4, null, 'Primary 2' )
into deathstar_power_nodes( id, primary_node_fk, label ) values ( 5, 4, 'Secondary 2.1' )
into deathstar_power_nodes( id, primary_node_fk, label ) values ( 6, null, 'Primary 3' )
select * from dual``````
6 row(s) inserted.
• Statement 4
``````create or replace view v_deathstar_grouped_power_nodes as
-- We first need to arrange the related nodes into groups
with node_groups as (
select group_entry.id group_id, -- Group-ID is the ID of the primary node
members.id member_id
from deathstar_power_nodes group_entry
-- We join the base-table with itself to get all
-- related entries for each row
inner join deathstar_power_nodes members
-- using either the primary-node reference,
-- or if it's NULL (because it *is* the
-- primary node) the ID
on group_entry.id = nvl(members.primary_node_fk, members.id)
-- We only do this for primary nodes
where group_entry.primary_node_fk is null
)
select nodes.id power_node_id,
groups.group_id,
groups.member_id,
member.label member_label,
case
when member.primary_node_fk is null then 1
else 0
end is_primary
from deathstar_power_nodes nodes
-- We join the groups via primary-node reference (if it exists) or the ID (of the primary node)
inner join node_groups groups
on nvl(nodes.primary_node_fk, nodes.id) = groups.group_id
inner join deathstar_power_nodes member
on groups.member_id = member.id``````
View created.
• Statement 5
``select * from v_deathstar_grouped_power_nodes where power_node_id = 1``
POWER_NODE_IDGROUP_IDMEMBER_IDMEMBER_LABELIS_PRIMARY
111Primary 11
112Secondary 1.10
113Secondary 1.20

3 rows selected.
• Statement 6
``select * from v_deathstar_grouped_power_nodes where power_node_id = 2``
POWER_NODE_IDGROUP_IDMEMBER_IDMEMBER_LABELIS_PRIMARY
211Primary 11
212Secondary 1.10
213Secondary 1.20

3 rows selected.
• Statement 7
``select * from v_deathstar_grouped_power_nodes where power_node_id = 3``
POWER_NODE_IDGROUP_IDMEMBER_IDMEMBER_LABELIS_PRIMARY
311Primary 11
312Secondary 1.10
313Secondary 1.20

3 rows selected.
• Statement 8
``select * from v_deathstar_grouped_power_nodes where power_node_id = 4``
POWER_NODE_IDGROUP_IDMEMBER_IDMEMBER_LABELIS_PRIMARY
444Primary 21
445Secondary 2.10

2 rows selected.
• Statement 9
``select * from v_deathstar_grouped_power_nodes where power_node_id = 5``
POWER_NODE_IDGROUP_IDMEMBER_IDMEMBER_LABELIS_PRIMARY
544Primary 21
545Secondary 2.10

2 rows selected.
• Statement 10
``select * from v_deathstar_grouped_power_nodes where power_node_id = 6``
POWER_NODE_IDGROUP_IDMEMBER_IDMEMBER_LABELIS_PRIMARY
666Primary 31