Query in 2 tables that have columns with the same name

Asked

Viewed 527 times

0

Good morning, I have two tables that have the column "st", when I pull the information comes from the table st "records", I would like to pull the "st" from the table "settings".

sql = "SELECT *   FROM configuracao b   LEFT JOIN dividas a on (b.registro = a.registro) and (b.empresa='" & Left(Combo1.Text, 2) & "')  left JOIN registros r on (b.registro = r.registro) and (b.empresa='" & Left(Combo1.Text, 2) & "')  ORDER BY r.data"


        While Not (rsconf.EOF)
              If (MSFlexGrid2.TextMatrix(li - 1, 0) = Left(rsconf("data"), 4)) And (ln > 1) Then li = li - 1
                            If rsconf("registro") <> "" Then Text1.Text = rsconf("registro")
                            If rsconf("nome") <> "" Then Label4.Caption = Trim(rsconf("nome"))
                            If rsconf("dt_adm") <> "" Then Label5.Caption = data_Normal(rsconf("dt_adm"))
                            If rsconf("st") <> "" Then
                                  MSFlexGrid1.TextMatrix(ln, 2) = "PENDÊNCIA"
                                  If rsconf("st") = "PA" Then MSFlexGrid1.TextMatrix(ln, 2) = "PARCELADO"
                            End If
                            If rsconf("dt_dem") <> "" Then Label7.Caption = data_Normal(rsconf("dt_dem"))
                            If rsconf("pag") <> "" Then
                               Label8.Caption = "NÃO"
                                If rsconf("pag") = "S" Then Label8.Caption = "SIM"
                            End If
                            v_dt = 0
                            If rsconf("data") <> "" Then v_dt = Mid(data_Normal(rsconf("data")), 4, 2)
                            Call encontra_mes
                            If rsconf("data") <> "" Then MSFlexGrid1.TextMatrix(ln, 0) = data_Normal(rsconf("data"))
                            If rsconf("valor") <> "" Then
                                  MSFlexGrid1.TextMatrix(ln, 1) = Format(rsconf("valor"), "#,##0.00")
                                If MSFlexGrid2.TextMatrix(li, cn) = "" Then
                                      MSFlexGrid2.TextMatrix(li, cn) = Format(rsconf("valor"), "#,##0.00")
                                Else
                                  MSFlexGrid2.TextMatrix(li, cn) = Format(CDbl(MSFlexGrid2.TextMatrix(li, cn)) + CDbl(rsconf("valor")), "#,##0.00")
                                End If
                                  v_ano = Left(rsconf("data"), 4)
                                  MSFlexGrid2.TextMatrix(li, 0) = v_ano
                            End If
                            If rsconf("valor_divida") <> "" Then v_td = Format(rsconf("valor_divida"), "#,##0.00")
                      ln = ln + 1
                      li = li + 1
                        rsconf.MoveNext
                        MSFlexGrid1.Rows = ln + 1
                        MSFlexGrid2.Rows = li + 1
                    Wend
sql-server join

  • Without seeing the query is very difficult to answer besides not knowing what has in each recordset, but in the query already tried to use configura.st?

  • I thought I put the query, sorry ^^'

  • You have the table structure to put?

  • "configuration" - > company/record/name/dt_adm/st/dt_dem/pag/usuario/dtu "records" -> company/record/date/value/user/dtu "divide" -> company/record/value_divide/user/dtu

  • select * will bring all the columns, the correct would be you correctly name the columns and only what you want, for example select configuracao.st, configuracao.registro.. from

1 answer

1


When using Joins it is good to name the oxen and not use *, because this can bring duplicity of information and confusion as this your doubt.

I imagine that in your Result you should have two ST and ST1 fields because SQL will rename when you find more than one field with the same name

I saw that you have the verification of the Company in the joins, the ideal would be to put in the Where because they are checking the main table "configuration"

That way sele select would look something like this

sql = "SELECT b.st, 'outros campos'   FROM configuracao b   
LEFT JOIN dividas a on (b.registro = a.registro)  
left JOIN registros r on (b.registro = r.registro)
where  (b.empresa='" & Left(Combo1.Text, 2) & "')
ORDER BY r.data"

See that I’m bringing the ST from table B and not from R

  • Thanks for the tips, I’ll try it this way. .

  • @Rodrigomerce managed to solve the problem? If it was from this answer, it is worth marking it as accepted (why accept an answer).

Browser other questions tagged sql-server join

You are not signed in. Login or sign up in order to post.