## DEV Community

Samuel Nitsche

Posted on • Originally published at cleandatabase.wordpress.com on

# #100CodeExamples – A challenge to learn and teach: Primary/Secondary Lookup

A while ago, Gregory Brown brought up the idea to do a “100 Code Examples” challenge:

In addition to #100DaysOfCode, it seems like #100CodeExamples would be a fun challenge to try.

Aim to produce 100 small annotated sample programs over the course of a year or two that illustrate all that you've learned.

— Practicing Developer (@practicingdev ) October 8, 2018

I liked the idea very much due to several reasons:

• The challenge will probably help me to track/be aware of what I learned
• Breaking down what I learned into small samples will reinforce the knowledge
• It will hopefully also exercise my skills to teach and share knowledge

I won’t put myself under pressure because that’s not the point of the challenge, but if I can manage I will post 1-2 examples on my blog here so the 100 Code Examples should be done in roughly a year.

The topics of the examples will most likely be related to my current work, so mainly SQL, PL/SQL and I will also try to add several examples around utPLSQL.

Every example will come with a very brief explanation of the goal and the critical snippet – hopefully annotated in a way that it’s understandable (I’d really appreciate your feedback on the latter part!).

If it’s possible I’ll also share a link to LiveSQL or a different environment where the example can be run immediately (won’t work with utPLSQL examples so easily).

# Example: Primary/Secondary Lookup

We often have Primary/Secondary situations and sometimes we need an easy way to get all related entries based of a single entry’s ID.

In my Star Wars example, the Deathstar operates on several power nodes, some of them have secondary nodes in case the primary power node is no longer working.

We want to create a view one can query with the ID of any power node, no matter whether a primary or secondary, and get the full list of related power nodes as result.

The base table of nodes is very simple:

Column Constraints
ID PK, NOT NULL
LABEL
PRIMARY_NODE_FK

The view could look like this:

``````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