Microsoft Dynamics AX (Archived)

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

(0) Share

Report

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

I have the same question (0)

All responses (6)

Answers (0)

Martin Dráb 237,987 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Miguel Zuniga 272 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Miguel Zuniga 272 on at

Like (0)

Report

I will create a new question for this..

Was this reply helpful? Yes No
Miguel Zuniga 272 on at

Like (0)

Report

resubmitted question. refer here (more detailed question)

Was this reply helpful? Yes No

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates