Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Can I apply exists joi...
Microsoft Dynamics AX (Archived)

Can I apply exists join to only one of the join hierarchy in a select statement?

(0) ShareShare
ReportReport
Posted on by Miguel Zuniga 272

Sorry if this question has no sense or if the question is badly written.. but I'm really curious. 

See code below:

select field1 from table1
    join field2 from table2
         where table1.field == table2.field
    join field3 from table3
         where table1.field == table3.field
    exists join table4
         where table3.field == table4.field

I'm curious about if I can only apply the exists join to table3 and not affect the entire select statement? is that possible? 

*This post is locked for comments

  • Miguel Zuniga Profile Picture
    Miguel Zuniga 272 on at
    RE: Can I apply exists join to only one of the join hierarchy in a select statement?

    resubmitted question. refer here (more detailed question)

  • Miguel Zuniga Profile Picture
    Miguel Zuniga 272 on at
    RE: Can I apply exists join to only one of the join hierarchy in a select statement?

    I will create a new question for this..

  • Martin Dráb Profile Picture
    Martin Dráb 233,544 Most Valuable Professional on at
    RE: Can I apply exists join to only one of the join hierarchy in a select statement?

    Can you please elaborate what you mean by your statement "I'm getting no returns because table4 has no connection with tables 1 and 2"? Maybe an example would help to demonstrate your point.

    As far as I can say, table4 is linked to table1 through table3. If you mean that there are no valid data for such a join, then using an inner join is a mistake.

    And the fact that table4 is not joined with table2 simply means that no filter is applied, so it can't cause your problem of not getting any data.

    Using an exists join for table3 wouldn't change which records are returned - all joins would stay in place; you would merely stop returning fields from table3.

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Can I apply exists join to only one of the join hierarchy in a select statement?

    Hi Miguel Zuñiga,

    It still does not make sense, you use inner join between table1, table2 and table3. So brackets do not change anything.

  • Miguel Zuniga Profile Picture
    Miguel Zuniga 272 on at
    RE: Can I apply exists join to only one of the join hierarchy in a select statement?

    Hi Martin.

    I plan to use exists join table4 in order to filter the value of field3.

    What's happening right now is that I'm getting no returns because table4 has no connection with tables 1 and 2.

    So I'm just wondering if I can use exists join for table3 only.

    Somewhat like this...

    select field1 from table1

       join field2 from table2

            where table1.field == table2.field

       [join field3 from table3

            where table1.field == table3.field

       exists join table4

            where table3.field == table4.field]

    Table3 and Table2 is joined to table1, but table4 is used only to filter table3.

    If it's still not understandable let me know.. I apologize in advance.

  • Martin Dráb Profile Picture
    Martin Dráb 233,544 Most Valuable Professional on at
    RE: Can I apply exists join to only one of the join hierarchy in a select statement?

    Unfortunately I don't understand your question.

    Your current code is possible - it will return table1.field1, table2.field2 and table3.field3. table4 will be used only for filtering, i.e. the query will return only such records that has a related record in table4.

    You can apply exists join to table3 too, but it'll clearly have an impact on what the query returns - it won't return table3.field3 anymore.

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

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Microsoft Dynamics AX (Archived)

#1
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 100 Super User 2025 Season 1

#2
Community Member Profile Picture

Community Member 48

#3
shanawaz davood basha Profile Picture

shanawaz davood basha 6

Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans