CODEWITHSUNDEEP
sql
PhilHQ
PhilHQ

Reputation: 58

Looping through entries

I'm an intern taking over a project from a previous intern at my company and I have very little database experience. The scope of the project has expanded and I need to get more data from a database. I have the following SQL query:

select
    DSR_SEGMENT_LIGNE.SEG_NOM as "name",
    DSR_NOEUD_SIMUL.NOE_NUMERO_NOEUD as "start",
    DSR_NOEUD_SIMUL_ARRIVEE.NOE_NUMERO_NOEUD as "end",
    DSR_SEGMENT_LIGNE.SEG_CIRCUIT as "circuit",
    case DSR_SEGMENT_LIGNE.SEG_R0_MANUELLE 
        when 0 then DSR_SEGMENT_LIGNE.SEG_R0
        else DSR_SEGMENT_LIGNE.SEG_R0_MANUELLE
    end as "r0",
    case DSR_SEGMENT_LIGNE.SEG_X0_MANUELLE 
        when 0 then DSR_SEGMENT_LIGNE.SEG_X0
        else DSR_SEGMENT_LIGNE.SEG_X0_MANUELLE
    end as "x0",
    case DSR_SEGMENT_LIGNE.SEG_R1_MANUELLE 
        when 0 then DSR_SEGMENT_LIGNE.SEG_R1
        else DSR_SEGMENT_LIGNE.SEG_R1_MANUELLE
    end as "r1",
    case DSR_SEGMENT_LIGNE.SEG_X1_MANUELLE 
        when 0 then DSR_SEGMENT_LIGNE.SEG_X1
        else DSR_SEGMENT_LIGNE.SEG_X1_MANUELLE
    end as "x1",
    sum(DSR_SECTION_LIGNE.SEC_LONGUEUR) as "length",
    DSR_SEGMENT_LIGNE.SEG_PARALLELE as "parallel",

from
    DSR_SEGMENT_LIGNE
    inner join DSR_NOEUD_SIMUL
        on DSR_SEGMENT_LIGNE.SEG_ID_NOEUD_DEPART
            = DSR_NOEUD_SIMUL.NOE_ID_NOEUD
    inner join DSR_NOEUD_SIMUL DSR_NOEUD_SIMUL_ARRIVEE
        on DSR_SEGMENT_LIGNE.SEG_ID_NOEUD_ARRIVEE
            = DSR_NOEUD_SIMUL_ARRIVEE.NOE_ID_NOEUD
    inner join DSR_LIGNE
        on DSR_LIGNE.LIG_ID_LIGNE
            = DSR_SEGMENT_LIGNE.LIG_ID_LIGNE
    inner join DSR_SECTION_LIGNE
        on DSR_SEGMENT_LIGNE.SEG_ID_SEGMENT
            = DSR_SECTION_LIGNE.SEG_ID_SEGMENT

where DSR_LIGNE.LIG_NOM = "3040"
group by
    DSR_SECTION_LIGNE.SEG_ID_SEGMENT
order by
    1, 2

Which outputs this:

name    start   end circuit r0                  x0                  r1                  x1                  length              parallel    
A       1370    1382    1   0,0005425630938234  0,00167906265425173 7,3195053173195E-5  0,00055148562601198 0,177540954416641   0   
B       1382    1383    1   0,0126386706603645  0,0378025148848846  0,00563519872024541 0,0131910224409082  3,70050627461981    1   
C       1382    1383    2   0,0126386706603645  0,0378025148848846  0,00563519872024541 0,0131910224409082  3,70050627461981    1   
D       1383    5515    1   0,021837386745766   0,0747186186014143  0,00948008366199402 0,023230913897742   6,82876115295014    0

The relevant element needed to gather additional data from other tables is the "name" (the four names in this case are A, B, C and D and are always unique). Is it possible to somehow loop through those four rows (something like: for each "name" do ...) without manually naming the name (where name = "A" for example) and gather name-specific data for each name and put that data in that name's row?

Upvotes: 0

Views: 48

Answers (1)

Gerrat
Gerrat

Reputation: 29690

You don't generally "loop through rows" with sql, so you need to think about this a different way.

It sounds like you want other tables joined to this query on name.

To do that just add more joins like:

inner join SOME_OTHER_TABLE 
    on SOME_OTHER_TABLE.SOME_COL = DSR_SEGMENT_LIGNE.SEG_NOM

Then just add whatever other columns from the other tables you wanted into your select.

I've made some simplifying assumptions here (there is a 1:1 correspondence between your original query and the rows of the other tables you'll be joining to). This may or may not be valid depending on what the actual relationship is, and what data you're trying to get from the other tables.

Upvotes: 3

Related Questions