Page 1 of 1

Loop with SQL queries... I'm a newbie!

Posted: Fri Nov 13, 2009 6:51 pm
by obrienmd
I haven't touched code since BASIC in about 1995 (please don't hold it against me) :)

I would like to code a loop which does the following:

For (# of times VariableA shows up in TableX)
SELECT ColumnM from TableX (nth occurance, N being stepped through in the loop), store as VariableB
SELECT ColumnN FROM TableY WHERE ColumnO = VariableB, store as VariableC
add VariableC to VariableD (running total)
Loop

Does this make any sense?

Re: Loop with SQL queries... I'm a newbie!

Posted: Fri Nov 13, 2009 7:13 pm
by requinix
If I'm reading that right you can do it all in one query. Just one.

Code: Select all

SELECT SUM(TableY.ColumnN) FROM TableY JOIN TableX ON TableX.ColumnM = TableY.ColumnO WHERE (some condition involving TableX)

Re: Loop with SQL queries... I'm a newbie!

Posted: Fri Nov 13, 2009 7:25 pm
by obrienmd
Thanks! Everything is making complete sense up to the "some condition involving tableX"... Any further explanation you can do on that?

Re: Loop with SQL queries... I'm a newbie!

Posted: Fri Nov 13, 2009 7:34 pm
by requinix
obrienmd wrote:Thanks! Everything is making complete sense up to the "some condition involving tableX"... Any further explanation you can do on that?
You didn't quite say how you figured "# of times VariableA shows up in TableX".

The condition is just whatever you use to total that number. Like if you want "# of times ColumnP is the value 'foo'" then the condition would be

Code: Select all

WHERE TableX.ColumnP = "foo"

Re: Loop with SQL queries... I'm a newbie!

Posted: Fri Nov 13, 2009 7:40 pm
by John Cartwright
For us to answer that, you will have to describe "# of times VariableA shows up in TableX" in more detail. Can VariableA exist within multiple columns, and is VariableA the only value in the columns? I.e. do you need to search for VariableA within the column value, or is it the column value?