Count Entity Relationships Including Zero Matches

Model the tables for a given scenario.

Define a normalized relational model that stores scenarios, their entities, and relationships between entities. Then write a query that reports every entity, including entities with no relationships.

Output

One row per entity with scenario_name, entity_name, and relationship_count
Include entities with zero relationships
Order by scenario_name, then entity_name ascending

Schema

scenario_definitions

Column	Type	Description
scenario_idPK	INT	Unique scenario identifier
scenario_name	VARCHAR(100)	Name of the modeled scenario
scenario_status	VARCHAR(20)	Optional lifecycle status

scenario_entities

Column	Type	Description
entity_idPK	INT	Unique entity identifier
scenario_id	INT	Scenario containing the entity
entity_name	VARCHAR(100)	Entity name
primary_key_name	VARCHAR(100)	Modeled primary key attribute name

scenario_relationships

Column	Type	Description
relationship_idPK	INT	Unique relationship identifier
source_entity_id	INT	Entity at the source end
target_entity_id	INT	Entity at the target end
relationship_name	VARCHAR(100)	Name of the relationship
relationship_type	VARCHAR(30)	Optional cardinality classification

Your question is Count Entity Relationships Including Zero Matches. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem