web

You’re offline. This is a read only version of the page.

News and Announcements icon

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... How to join a LEFT JOI...

Finance | Project Operations, Human Resources, ...

Suggested Answer

How to join a LEFT JOIN a table on table1.column1 <> '' in a data entity query

(0) Share

Report

Report

Posted on by Superbunny

329

I have two tables and want to LEFT JOIN them in the following SQL way:

FROM Table1
LEFT OUTER JOIN table2 ON (Table1.OtherField <> '' AND Table1.IdField = Table2.IdField)

In SQL I simply I can do it like this, but how can I do the same thing in a data entity?

I tried using range, but that obviously creates a WHERE clause and therefore all the rows where Table1.Otherfield are emtpty get filtered out...

I have the same question (0)

All responses Img

All responses (5)

Answers Img

Answers (0)

Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Hi, Please Check this old thread

community.dynamics.com/.../869739

Was this reply helpful? Yes No
Superbunny 329 on at

Like (0)

Report

This old threat is about creating a LEFT JOIN, of which I already have multiple ones in my query.

The question is about adding the 'ON table1.column1 <> ''' part of the LEFT OUTER JOIN, which I cannot figure out how to add via editor.

When creating a view I am in the same editor as well.

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Not sure if it will help but have you tried adding relation for Table1.IdField = Table2.IdField and range on 'ON table1.column1 <> '''.

Was this reply helpful? Yes No
Superbunny 329 on at

Like (0)

Report

Yes, I tried using range, but that obviously creates a WHERE clause and therefore all the rows where Table1.Otherfield are emtpty get filtered out. LEFT JOINing would never filter out that data

Was this reply helpful? Yes No
Suggested answer

Muhammad Saad Salman 29 on at

Like (0)

Report

Unfortunately,

This type of condition is not allowed at the relation node level. You cannot include an empty string in the relationship node. In field-fixed and related field-fixed it allows for relations based on enums but not for string field based conditions.

You can write a computed column which acts as subquery on SQL level on the entity level.

Thanks,

Was this reply helpful? Yes No

Join the conversation

Helpful resources

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1

Martin Dráb 646 Most Valuable Professional

#2

André Arnaud de Cal... 529 Super User 2025 Season 2

#3

Sohaib Cheema 285 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans