Unanswered

Power Apps Lookup/SQL Server inner join not working?

(0) Share

Report

Posted on by hanzelmans

I have two tables on SQL Server.

Table A:

EmployeeID	FollowingID	StudentID
2	1	8
2	2	9
2	3	7

Table B has ten columns with the common key between the two tables being the StudentID. Table B has 500 rows.

Using the info in Table A, an inner join should return three rows of data.

However, if I use this code it returns 500 rows.

ClearCollect(colJoin,
 AddColumns(vw_Students_CaseNumber,"FollowingNumber",LookUp(colFollowing,personID=colFollowing[@StudentID],FollowingID)))

If I use this code, it returns three rows.

ClearCollect(colJoin, AddColumns(colFollowing,"CaseNumber",LookUp(vw_Students_CaseNumber,StudentID=vw_Students_CaseNumber[@personID],casenumber)))

I want to use the former code example as that table has all of the columns, except one, that I want.

Why isn't this working? What am I missing?

Thanks,

Steve

Categories:

Connecting to Data

All responses (4)

Answers (0)

hanzelmans on at

Like (1)

Report

Re: Power Apps Lookup/SQL Server inner join not working?

Hello @timl
Thanks for your response.

When I saw the results of those blocks of code that was exactly what I thought (left outer). I basically copied it from a few different sites where they were giving that as an example of an inner join. Hence, my confusion.

Regardless, I am going forward with using views.

Thanks again,
Steve
timl 34,933 Super User 2025 Season 1 on at

Like (0)

Report
Re: Power Apps Lookup/SQL Server inner join not working?

Hi @hanzelmans

I'd highly recommend @GeekAlf_Pro 's advice of carrying out the join in view.

The syntax below is the SQL equivilent of a 'left outer' join (rather than an 'inner join'), which is the reason why it returns 500 rows.

ClearCollect(colJoin, AddColumns(vw_Students_CaseNumber,"FollowingNumber",LookUp(colFollowing,personID=colFollowing[@StudentID],FollowingID)))
hanzelmans on at

Like (1)

Report

Re: Power Apps Lookup/SQL Server inner join not working?

@GeekAlf_Pro
I actually have that working right now, but wasn't sure if I should/could be doing it in the application.

I'm sure the performance is much better using views, however, I'm a relative novice in regards to Power Apps so I'm trying a few different things.

Thanks,
Steve
GeekAlf_Pro 400 on at

Like (1)

Report

Re: Power Apps Lookup/SQL Server inner join not working?

Hi @hanzelmans

Personnally, i prefer to use views in my sql database.
Do you have the possibility to create one ?
you'll be able to use the right or left join in sql