web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / 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) ShareShare
ReportReport
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)
  • Suggested answer
    Muhammad Saad Salman Profile Picture
    Muhammad Saad Salman 29 on at

    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,

  • Superbunny Profile Picture
    Superbunny 329 on at

    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

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at

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

  • Superbunny Profile Picture
    Superbunny 329 on at

    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.

  • Mohit Rampal Profile Picture
    Mohit Rampal 12,565 Moderator on at

    Hi, Please Check this old thread

    community.dynamics.com/.../869739

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

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

#1
Abhilash Warrier Profile Picture

Abhilash Warrier 843 Super User 2025 Season 2

#2
André Arnaud de Calavon Profile Picture

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

#3
Martin Dráb Profile Picture

Martin Dráb 322 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans