Making a SQL Join of multiple tables with PowerApps

[AlbertoLopez19]

I need to show on a Gallery the information of the next SQL query:

SELECT necesidades_empleado.*, catalogo_CF.*, catalogo_CF.TIPO, empleados.[E-mail]
FROM (necesidades_empleado INNER JOIN catalogo_CF ON necesidades_empleado.Id_curso = catalogo_CF.Id_curso) INNER JOIN empleados ON necesidades_empleado.ID_empleado = empleados.ID
WHERE (((catalogo_CF.TIPO)="FORMACIÓN"));

I tried using something like this, but I just get errors everywhere:

ClearCollect(colNecesidadesMyGrowth;
     AddColumns(
          necesidades_empleado,
          "catalogo_CF", Filter(catalogo_CF, Id_curso=necesidades_empleado[@Id_curso]),
          "empleados", Filter(empleados, ID_empleado=empleados[@ID])
     )
)

How could I do it?

[StalinPonnusamy]

Hi @AlbertoLopez19 

AddColumns means we are adding a new column with a single value from the table. Filter returns a table. So we have to use First and ColumnName from the Second Table to grab it.

ClearCollect(colNecesidadesMyGrowth,
     AddColumns(
          necesidades_empleado,
          "catalogo_CF", First(Filter(catalogo_CF, Id_curso=necesidades_empleado[@Id_curso])).ColumnName,
          "empleados", First(Filter(empleados, ID_empleado=empleados[@ID])).ColumnName
     )
)

Thanks,
Stalin - Learn To Illuminate

[StalinPonnusamy]

Hi @AlbertoLopez19 

Sample Query for reference

ClearCollect(
    Col2Tables,
    AddColumns(
        StudentTest,
        "TeacherName",
        First(
            Filter(
                Teacher,
                TeacherID = ThisRecord.TeacherID
            )
        ).TeacherName
    )
)

Result

SQL DataSource

Thanks,
Stalin - Learn To Illuminate

[StalinPonnusamy]

Hi @AlbertoLopez19 

Please let us know if anything needs on your post. We can help with this.

Please do not forget to give kudos if you find the suggestion helpful or Accept it as a solution if works fine to help other users to find it useful.

Thanks,
Stalin - Learn To Illuminate

[MikeAA]

Dear Sir, I was wondering  if in Power Apps I can create a collection from some SQL tables where I can display "all the records" from the Customers Table that are associated with each "Nombre"  from the Table "Tabla_Usuarios".
I´m asking because of the limitations Power Apps has of only being able to filter by First, Last or LookUp.

Appreciate if you can give me a few minutes of your time.

Thanks in advance

[StalinPonnusamy]

Hi @MikeAA 

This is possible in PowerApps, but achieving it takes a few steps. My preference is to create SQL Stored procedure and call it from PowerApps. Get the result in a collection and use it.

Please let me know if you need any help on this.

Thanks, Stalin (Microsoft MVP)
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[MikeAA]

Thank you for your response. Appreciate it

[Sairam_pera]

It is working Perfectly But Why that guy is not accepted as a solution