Query in 2 tables that have columns with the same name

0

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
    
asked by anonymous 24.08.2018 / 13:50

1 answer

0

When using JOINs it is good to name the cattle and not to use *, as this can bring double information and confusion as you doubt it.

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 Company check in joins, ideally would be put in the Where they are checking the main table "configuration"

That way select 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 of table B and not of R

    
24.08.2018 / 14:23