CODEWITHSUNDEEP
t-sqlforeach
salvationishere
salvationishere

Reputation: 3511

How to formulate FOR EACH loop in SQL?

I am developing a SQL query that currently works for one group/mode combination. But I want this query to work for all groups and modes. And include each combination one time only. But in source data there are many other columns, so there is more than one record per combination.

So my source table structure is:

group   mode   p-val
-----   ----   ------
A       B      4.567
C       D      3.694

How can I setup a loop to loop through each group/mode combination? And I'd rather not use cursors!

Upvotes: 1

Views: 2379

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40319

A very broad and general question. Here's a broad and general answer.

First, write a query to extract the set of the values you are interested in, such that each set is unique. SELECT DISTINCT... and SELECT... GROUP BY... suggest themselves.

This data could be stored as a temp table, included as a subquery, or just made part of the overall final query. The best to use depends entirely on what you're working on.

Next, write your "main" processing query using this subset as a basis. Something like ...FROM <theSubset> INNER JOIN <etc>.

Done properly (I'm not saying it'd be easy), the effects of this set-based methodology are quite similar to the results achieved with procedural looping.

Upvotes: 2

ahmet alp balkan
ahmet alp balkan

Reputation: 45224

This is called JOIN in sql. i.e. the statement

SELECT * FROM table AS a JOIN table AS b;

results 

a | b | 4.5   | a | b | 4.5
a | b | 4.5   | c | d | 3.694
c | d | 3.694 | a | b | 4.5
c | d | 3.694 | c | d | 3.694

now you have all combinations of [group,mode] records. Read more on JOINs from Google.

Upvotes: 2

Related Questions