List of Common SQL Queries

[isau]

Has anyone already started compiling a resource of common SQL queries? There are a couple I use over and over, particularly mapping between Requirements and RequirementSets, and the various paths through the Modifiers tables. I keep them saved on my hard drive but wouldn't mind sharing some of them and revising them with the community. Should I start a thread for them or is there a place they are discussed already?

 

[isau]

Ok no one bit so far so I'll post a few examples. I could start a "real" thread somewhere with these, or we could host it elsewhere. Partly I'm posting this here so if my harddrive suddenly wipes I dont have to recreate these by hand.

I included a WHERE statement with a LIKE clause at the end of each query because I often want to refine the search a little.

LeaderTraits to Modifiers and ModifierArguments

select * from leadertraits
left join traits on leadertraits.TraitType = traits.TraitType
left join traitmodifiers on traitmodifiers.TraitType = leadertraits.TraitType
left join modifiers on modifiers.ModifierId = traitmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.modifierid
where leadertraits.LeaderType like '%%' ;

CivilizationTraits to Modifiers and ModifierArguments

select * from civilizationtraits
left join traits on civilizationtraits.TraitType = traits.TraitType
left join traitmodifiers on traitmodifiers.TraitType = CIVILIZATIONtraits.TraitType
left join modifiers on modifiers.ModifierId = traitmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.modifierid
where civilizationtraits.civilizationtype like '%%' ;

Policies to Modifiers and ModifierArguments

select * from policies as p
left join policymodifiers as pm on pm.PolicyType = p.PolicyType
left join modifiers as m on m.ModifierId = pm.ModifierId
left join modifierarguments as ma on ma.ModifierId = m.ModifierId
where m.ModifierType like '%%'

RequirementSets to Requirements and RequirementArguments

select * from requirementsets
left join requirementsetrequirements on requirementsetrequirements.RequirementSetId = requirementsets.RequirementSetId
left join requirements on requirements.requirementid = requirementsetrequirements.RequirementId
left join requirementarguments on requirementarguments.RequirementId = requirements.requirementid
where requirementsets.requirementsetid like '%%'

Beliefs to Modifiers and Modifier Arguments, including secondary dive to sublevel Modifiers and ModifierArguments, along with RequirementSets and Arguments

select * from beliefs
inner join beliefmodifiers on beliefmodifiers.BeliefType = beliefs.BeliefType
inner join modifierarguments as MA1 on ma1.ModifierId = beliefmodifiers.ModifierID
inner join modifiers on MA1.Value = modifiers.ModifierId
left join modifierarguments as Ma2 on Ma2.ModifierId = MA1.Value
left join requirementsets on requirementsets.RequirementSetId = modifiers.SubjectRequirementSetId
left join requirementsetrequirements on requirementsetrequirements.RequirementSetId = requirementsets.RequirementSetId
left join requirementarguments on requirementarguments.RequirementId = requirementsetrequirements.RequirementId

UnitPromotions to Modifiers and ModifierArguments

select * from unitpromotions
left join unitpromotionmodifiers on unitpromotionmodifiers.UnitPromotionType = unitpromotions.UnitPromotionType
left join modifiers on modifiers.ModifierId = unitpromotionmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.ModifierId
where unitpromotions.UnitPromotionType like '%%'

RandomAgendas to Modifiers and ModifierArguments

select * from randomagendas
left join agendas on agendas.AgendaType = randomagendas.AgendaType
left join agendatraits on agendatraits.AgendaType = randomagendas.AgendaType
left join traits on traits.traittype = agendatraits.traittype
left join traitmodifiers on traitmodifiers.TraitType = traits.TraitType
left join modifiers on modifiers.ModifierId = traitmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.ModifierId

Combine all Start Biases (Feature, Terrain, Resource and River) into a Single Table

select civilizationtype, terraintype as biastype, "TERRAIN" as biastype_category, tier from startbiasterrains
union
select civilizationtype, featuretype as biastype, "FEATURE" as biastype_category, tier from startbiasfeatures
union
select civilizationtype, resourcetype as biastype, "RESOURCE" as biastype_category, tier from startbiasresources
union
select civilizationtype, "RIVER" as biastype, "RIVER" as biastype_category, tier from startbiasrivers
order by civilizationtype

 

[raen]

Maybe for tutorials section nice tips