Loops in SQL

Thalassicus

Bytes and Nibblers
Joined
Nov 9, 2005
Messages
11,057
Location
Texas
Moderator Action: ATTENTION, please see post #3 in this thread for the SQL information :).

Anyone have tips for writing loops in sql? I've always struggled to translate even simple algorithms like the one below. I eventually manage, but it takes me an unusually long time compared to other code formats.

"For each resource Moai can improve, copy the yields from the normal improvement for that resource."

It's easy to write in lua pseudocode...

PHP:
for _, impType in pairs({'IMPROVEMENT_MOAI'}) do  --table allows scalability
  for impResourceInfo in GameInfo.Improvement_ResourceTypes{ImprovementType = impType} do
    for impYieldInfo in GameInfo.Improvement_ResourceType_Yields{ResourceType = impResourceInfo.ResourceType} do
      if not GameInfo.Improvements[impYieldInfo.ImprovementType].CreatedByGreatPerson then
        INSERT INTO Improvement_ResourceType_Yields
        (ImprovementType, ResourceType, YieldType, Yield)
        impType, impResourceInfo.ResourceType, impYieldInfo.YieldType, impYieldInfo.Yield
      end
    end
  end
end
When I try to translate that to nested sql select statements, my mind gets turned around into spaghetti. :lol:

Does anyone know a good starting point? Would it be easier to start at the innermost loop and work outwards... or outer loop inwards? Is there a general pattern you follow for writing loops in sql?
 
Training is everything.
You don't have to start with the total overkill. You could already get rid of one loop in the example by using a single WHERE clause. And that is not that complicated, isn't it ;)?


Don't have a clue of Civ5 or SQLite (just MySQL), and I saw this here just by accident, so I'm again out :mischief:.
 
Like most things, you eat an elephant one bite at a time from the outside inwards

Code:
--table allows scalability
[B][COLOR="Red"]for _, impType in pairs({'IMPROVEMENT_MOAI'}) do  
end[/COLOR][/B]

Code:
[B][COLOR="red"]SELECT * 
FROM Improvements i 
WHERE i.Type IN ('IMPROVEMENT_MOAI')[/COLOR][/B]


Code:
for _, impType in pairs({'IMPROVEMENT_MOAI'}) do
  [B][COLOR="red"]for impResourceInfo in GameInfo.Improvement_ResourceTypes{ImprovementType = impType} do
  end[/COLOR][/B]
end

Code:
SELECT * 
FROM Improvements i, [B][COLOR="red"]Improvement_ResourceTypes rt[/COLOR][/B] 
WHERE i.Type IN ('IMPROVEMENT_MOAI') [B][COLOR="red"]AND rt.ImprovementType = i.Type[/COLOR][/B]


Code:
for _, impType in pairs({'IMPROVEMENT_MOAI'}) do
  for impResourceInfo in GameInfo.Improvement_ResourceTypes{ImprovementType = impType} do
    [B][COLOR="red"]for impYieldInfo in GameInfo.Improvement_ResourceType_Yields{ResourceType = impResourceInfo.ResourceType} do
    end[/COLOR][/B]
  end
end

Code:
SELECT * 
FROM Improvements i, Improvement_ResourceTypes rt, 
[B][COLOR="red"]Improvement_ResourceType_Yields y[/COLOR][/B] 
WHERE i.Type IN ('IMPROVEMENT_MOAI') AND rt.ImprovementType=i.Type 
[B][COLOR="red"]AND y.ResourceType=rt.ResourceType[/COLOR][/B]


Code:
for _, impType in pairs({'IMPROVEMENT_MOAI'}) do
  for impResourceInfo in GameInfo.Improvement_ResourceTypes{ImprovementType = impType} do
    for impYieldInfo in GameInfo.Improvement_ResourceType_Yields{ResourceType = impResourceInfo.ResourceType} do
      [B][COLOR="red"]if not GameInfo.Improvements[impYieldInfo.ImprovementType].CreatedByGreatPerson then
      end[/COLOR][/B]
    end
  end
end

Code:
SELECT * 
FROM Improvements i, Improvement_ResourceTypes rt, 
Improvement_ResourceType_Yields y, [COLOR="red"][B]Improvements gp[/B][/COLOR] 
WHERE i.Type IN ('IMPROVEMENT_MOAI') AND rt.ImprovementType=i.Type 
AND y.ResourceType=rt.ResourceType 
[B][COLOR="red"]AND gp.Type=y.ImprovementType AND NOT gp.CreatedByGreatPerson[/COLOR][/B]


Code:
for _, impType in pairs({'IMPROVEMENT_MOAI'}) do
  for impResourceInfo in GameInfo.Improvement_ResourceTypes{ImprovementType = impType} do
    for impYieldInfo in GameInfo.Improvement_ResourceType_Yields{ResourceType = impResourceInfo.ResourceType} do
      if not GameInfo.Improvements[impYieldInfo.ImprovementType].CreatedByGreatPerson then
        [B][COLOR="red"]SELECT impType, impResourceInfo.ResourceType, impYieldInfo.YieldType, impYieldInfo.Yield[/COLOR][/B]
      end
    end
  end
end

Code:
SELECT [B][COLOR="red"]i.Type, rt.ResourceType, y.YieldType, y.Yield[/COLOR][/B] 
FROM Improvements i, Improvement_ResourceTypes rt, Improvement_ResourceType_Yields y, Improvements gp 
WHERE i.Type IN ('IMPROVEMENT_MOAI') AND rt.ImprovementType=i.Type 
AND y.ResourceType=rt.ResourceType 
AND gp.Type=y.ImprovementType AND NOT gp.CreatedByGreatPerson

Code:
for _, impType in pairs({'IMPROVEMENT_MOAI'}) do 
  for impResourceInfo in GameInfo.Improvement_ResourceTypes{ImprovementType = impType} do
    for impYieldInfo in GameInfo.Improvement_ResourceType_Yields{ResourceType = impResourceInfo.ResourceType} do
      if not GameInfo.Improvements[impYieldInfo.ImprovementType].CreatedByGreatPerson then
        [B][COLOR="red"]INSERT INTO Improvement_ResourceType_Yields
        (ImprovementType, ResourceType, YieldType, Yield)[/COLOR][/B]
        impType, impResourceInfo.ResourceType, impYieldInfo.YieldType, impYieldInfo.Yield
      end
    end
  end
end

Code:
[B][COLOR="red"]INSERT INTO Improvement_ResourceType_Yields(ImprovementType, ResourceType, YieldType, Yield)[/COLOR][/B] 
SELECT i.Type, rt.ResourceType, y.YieldType, y.Yield 
FROM Improvements i, Improvement_ResourceTypes rt, Improvement_ResourceType_Yields y, Improvements gp 
WHERE i.Type IN ('IMPROVEMENT_MOAI') AND rt.ImprovementType=i.Type 
AND y.ResourceType=rt.ResourceType 
AND gp.Type=y.ImprovementType AND NOT gp.CreatedByGreatPerson

SQLiteSpy **IS** your friend when doing this!!!
 
When I try to translate that to nested select statements, ...

Which is probably why it's so hard, as 99 times out of 100 you should be using JOINs
 
Thanks for pointing me to sqlitespy! :goodjob: That step-by-step is incredibly helpful. Now I see why I was having such trouble. I tried using the "join" operator and couldn't get that to work, then did searches and found people saying sqlite doesn't support it in update statements, so I assumed nested selects were necessary. Is that list of tables in the "from" clause an implied join, without the explicit word?

I think the main problem is a lack of examples to look at. It's challenging to figure out a language with just a reference site. Your example here helps a lot!
 
Is that list of tables in the "from" clause a similar concept to a join?

Code:
SELECT * FROM A a, B b WHERE a.id=b.id

The a.id=b.id bit is the join (where you link/join one table to another), assigning shortcuts (or whatever the SQL term is) to the tables (the A a and B b bits) just makes it a lot easier to write and also allows you to use the same table more than once in different ways (ie the Improvements i, Improvements gp bit)

It's a very, very long time since I read an SQL book (probably about Oracle 6 or 7) - try w3schools I find most of their stuff very good, or Dev Guru if you want a basic reference, failing that, try the SQLite docs

PS: Stop editing your questions as people are replying! ;)
 
It's a bad habit of a perfectionist. :crazyeye:

Looking at the earlier code, is it possible to combine the i and gp shortcuts?

Edit: nevermind, I see now why they must be separate.
 
is it possible to combine the i and gp shortcuts?

No. How would you re-write your Lua outer loop to include the if condition?


You ....

Stop it, just stop it! Step away from the Edit button!!!
 
At least I marked it as an edit this time... :lol:

This helped me figure out how to do another problem as well:

Code:
INSERT INTO Improvement_ResourceTypes(ImprovementType, ResourceType) 
SELECT improve.Type, res.Type
FROM Improvements improve, Resources res
WHERE improve.Type IN (
    'IMPROVEMENT_ACADEMY'
)
AND res.Happiness > 0
AND NOT res.TechCityTrade = 'TECH_SAILING';

Without that join, statements like this would have been very difficult to accomplish. Thank you! This has made my life so much easier.
 
The statements I wrote work when executed in sqlitespy, but don't appear to be having an effect ingame. Do you know why this might be? I added the pottery line to confirm the statements are executing.
Code:
INSERT OR REPLACE INTO Improvement_ResourceTypes(ImprovementType, ResourceType) 
SELECT improve.Type, res.Type
FROM Improvements improve, Resources res
WHERE (
  improve.CreatedByGreatPerson = 1
  OR improve.Type IN ('IMPROVEMENT_MOAI', 'IMPROVEMENT_TERRACE_FARM')
)
AND NOT res.TechCityTrade = 'TECH_SAILING';

INSERT OR REPLACE INTO Improvement_ResourceType_Yields(ImprovementType, ResourceType, YieldType, Yield) 
SELECT improve.Type, resTypes.ResourceType, resYields.YieldType, resYields.Yield 
FROM Improvements improve, Improvement_ResourceTypes resTypes, Improvement_ResourceType_Yields resYields, Improvements impBasic
WHERE (
  improve.CreatedByGreatPerson = 1
  OR improve.Type IN ('IMPROVEMENT_MOAI', 'IMPROVEMENT_TERRACE_FARM')
)
AND resTypes.ImprovementType = improve.Type
AND resTypes.ResourceType = resYields.ResourceType
AND resYields.ImprovementType = impBasic.Type AND NOT (
impBasic.CreatedByGreatPerson = 1
OR impBasic.Type IN ('IMPROVEMENT_MOAI', 'IMPROVEMENT_TERRACE_FARM')
);

UPDATE Technologies SET Cost = 999 WHERE Type = 'TECH_POTTERY';

--
This is intended to make the unique and great improvements buildable on all resources, with the same yield bonuses as the basic improvements.
 
Probably because in stopwatch.log (yeah, how many people look in there ;) ) this line

Code:
[48607.056] , ActivateModsAndDLCForEnabledMods - Framework, 2.365181

appears after all the mod XML/SQL load lines

So looks like IMPROVEMENT_MOAI doesn't exist when you execute your SQL

EDIT: Nope, not that - simple test shows that despite that log line the DLC mods are loaded first
 
Ummm ... your code works fine in my mod

Code:
> for i in GameInfo.Improvement_ResourceTypes{ImprovementType="IMPROVEMENT_MOAI"} do print(i.ImprovementType, i.ResourceType) end
 WorldView: IMPROVEMENT_MOAI	RESOURCE_IRON
 WorldView: IMPROVEMENT_MOAI	RESOURCE_HORSE
 WorldView: IMPROVEMENT_MOAI	RESOURCE_COAL
 WorldView: IMPROVEMENT_MOAI	RESOURCE_OIL
 WorldView: IMPROVEMENT_MOAI	RESOURCE_ALUMINUM
 WorldView: IMPROVEMENT_MOAI	RESOURCE_URANIUM
 WorldView: IMPROVEMENT_MOAI	RESOURCE_COW
 WorldView: IMPROVEMENT_MOAI	RESOURCE_SHEEP
 WorldView: IMPROVEMENT_MOAI	RESOURCE_DEER
 WorldView: IMPROVEMENT_MOAI	RESOURCE_BANANA
 WorldView: IMPROVEMENT_MOAI	RESOURCE_STONE
 WorldView: IMPROVEMENT_MOAI	RESOURCE_GOLD
 WorldView: IMPROVEMENT_MOAI	RESOURCE_SILVER
 WorldView: IMPROVEMENT_MOAI	RESOURCE_GEMS
 WorldView: IMPROVEMENT_MOAI	RESOURCE_MARBLE
 WorldView: IMPROVEMENT_MOAI	RESOURCE_IVORY
 WorldView: IMPROVEMENT_MOAI	RESOURCE_FUR
 WorldView: IMPROVEMENT_MOAI	RESOURCE_DYE
 WorldView: IMPROVEMENT_MOAI	RESOURCE_SPICES
 WorldView: IMPROVEMENT_MOAI	RESOURCE_SILK
 WorldView: IMPROVEMENT_MOAI	RESOURCE_SUGAR
 WorldView: IMPROVEMENT_MOAI	RESOURCE_COTTON
 WorldView: IMPROVEMENT_MOAI	RESOURCE_WINE
 WorldView: IMPROVEMENT_MOAI	RESOURCE_INCENSE

Or have I missed something?
 
I should have been specific: when I placed an academy on a deer tile ingame it provided only the science bonus, missing the basic yields.

Edit: nevermind, it was just an unrelated issue with deer. Thanks!
 
I'd like to ask a slightly different question. I want to subtract up to (3 - tech column * 0.5) from the maintenance of several units. How do I do this? Here is what I have so far:

Code:
UPDATE Units
SET ExtraMaintenanceCost = ExtraMaintenanceCost - MAX(0, 3 - tech.GridX * 0.5)
WHERE Type IN (
  SELECT unit.Type 
  FROM Units unit, Technologies tech
  WHERE unit.PrereqTech = tech.Type
);
 
Code:
UPDATE Units
  SET ExtraMaintenanceCost = ExtraMaintenanceCost -
    (SELECT MAX(0, 3 - t.GridX * 0.5) FROM Technologies t WHERE Units.PrereqTech = t.Type);
 
Ah thank you!

I have trouble with sql logic because the algorithm structure is so different from c-style programming languages. It's weird to use a SELECT statement, which usually returns a list, for something expecting a single value. What happens if the select was changed to actually return a list of values?
 
What happens if the select was changed to actually return a list of values?

I believe it is implementation dependant, in that it will a) be reported as an error, b) take the first value in the results set, or c) perform the operation for every value
 
Top Bottom