Inner Join with more than a field
- by Leandro
Precise to do a select with inner join that has relationship in more than a field among the tables
Exemple:
DataSet dt = new Select().From(SubConta.Schema)
.InnerJoin(PlanoContabilSubConta.EmpSubContaColumn, SubConta.CodEmpColumn)
.InnerJoin(PlanoContabilSubConta.FilSubContaColumn, SubConta.CodFilColumn)
.InnerJoin(PlanoContabilSubConta.SubContaColumn, SubConta.TradutorColumn)
.Where(PlanoContabilSubConta.Columns.EmpContabil).IsEqualTo(cEmp)
.And(PlanoContabilSubConta.Columns.FilContabil).IsEqualTo(cFil)
.And(PlanoContabilSubConta.Columns.Conta).IsEqualTo(cTrad)
.ExecuteDataSet();
But the generated sql is wrong:
exec sp_executesql N'/* GetDataSet() */ SELECT [dbo].[SubContas].[CodEmp], [dbo].[SubContas].[CodFil], [dbo].[SubContas].[Tradutor], [dbo].[SubContas].[Descricao], [dbo].[SubContas].[Inativa], [dbo].[SubContas].[DataImplantacao]
FROM [dbo].[SubContas]
INNER JOIN [dbo].[PlanoContabilSubContas] ON [dbo].[SubContas].[CodEmp] = [dbo].[PlanoContabilSubContas].[EmpSubConta]
INNER JOIN [dbo].[PlanoContabilSubContas] ON [dbo].[SubContas].[CodFil] = [dbo].[PlanoContabilSubContas].[FilSubConta]
INNER JOIN [dbo].[PlanoContabilSubContas] ON [dbo].[SubContas].[Tradutor] = [dbo].[PlanoContabilSubContas].[SubConta]
WHERE EmpContabil = @EmpContabil0
AND FilContabil = @FilContabil1
AND Conta = @Conta2
',N'@EmpContabil0 varchar(1),@FilContabil1 varchar(1),@Conta2 varchar(1)',@EmpContabil0='1',@FilContabil1='1',@Conta2='1'
What should be made to generate this sql?
exec sp_executesql N'/* GetDataSet() */ SELECT [dbo].[SubContas].[CodEmp], [dbo].[SubContas].[CodFil], [dbo].[SubContas].[Tradutor], [dbo].[SubContas].[Descricao], [dbo].[SubContas].[Inativa], [dbo].[SubContas].[DataImplantacao]
FROM [dbo].[SubContas]
INNER JOIN [dbo].[PlanoContabilSubContas] ON [dbo].[SubContas].[CodEmp] = [dbo].[PlanoContabilSubContas].[EmpSubConta] AND
[dbo].[SubContas].[CodFil] = [dbo].[PlanoContabilSubContas].[FilSubConta] AND
[dbo].[SubContas].[Tradutor] = [dbo].[PlanoContabilSubContas].[SubConta]
WHERE EmpContabil = @EmpContabil0
AND FilContabil = @FilContabil1
AND Conta = @Conta2
',N'@EmpContabil0 varchar(1),@FilContabil1 varchar(1),@Conta2 varchar(1)',@EmpContabil0='1',@FilContabil1='1',@Conta2='1'
