Advantage Database Forum

Question

I have an order system. Users with can be attached to different orders as a type of different user. They can download documents associated with an order. Documents are only given to certain types of users on the order. I'm having trouble writing the query to check a user's permission to view a document and select the info about the document.

I have the following tables and (applicable) fields:

Docs: DocNo, FileNo
DocAccess: DocNo, UserTypeWithAccess
FileUsers: FileNo, UserType, UserNo

I have the following query:

SELECT Docs.* 
FROM Docs
WHERE DocNo = 1000
  AND EXISTS (
         SELECT * FROM DocAccess
         LEFT JOIN FileUsers
           ON FileUsers.UserType = DocAccess.UserTypeWithAccess 
           AND FileUsers.FileNo = Docs.FileNo /* Errors here */
         WHERE DocAccess.UserNo = 2000 )

The trouble is that in the Exists Select, it does not recognize Docs (at Docs.FileNo) as a valid table. If I move the second on argument (FileUsers.FileNo = Docs.FileNo) to the where clause rather than the join, then it does identify the alias, but I would rather limit the initial join rather than filter them out after the fact. This also shows the intent more clearly to me.

Why doesn't this work?

In ADS 10.

Accepted Answer

This is indeed an erroneous limitation in the query engine. The Join Condition can only contain columns from table in the join group or constants. In this construct, although the Docs.FileNo is a table column, it should be treated as a constants because its value is from the parent query and is constant during each execution of the child query.

I will log this in our bug tracker and have it fixed in the next update release.

Thank you for reporting the bug. I do not know a workaround except the one you already mentioned.

Answer 2

1

Within the LEFT JOIN definition you can only use those tables in the ON condition that are part of the JOIN. Having not too much other experiance with other SQL database I would still expect this to be the case elsewhere as well.

I do not believ that doing an EXISTS() on a OUTER join is a good idea here as the OUTER join (a LEFT join is an outer join) will find a row even if there is no matching record in FileUsers. As the only WHERE close inside the EXISTS() part is based on the left table DocAccess, the EXISTS statement probably returns TRUE, as soon as there is a DocAccess row with UserNo 2000, even if it does not match the ON condition. Do you get what I mean?

SELECT Docs.* 
FROM Docs
WHERE DocNo = 1000
AND EXISTS (
         SELECT * FROM DocAccess
         INNER JOIN FileUsers
           ON FileUsers.UserType = DocAccess.UserTypeWithAccess 
         WHERE FileUsers.FileNo = Docs.FileNo 
         AND DocAccess.UserNo = 2000 )

should do the trick I think.

link

answered 15 Jan, 13:26

Stephan Leiw...
821●1●14●33
accept rate: 10%

edited 15 Jan, 13:48

Alex Wong
4.3k●22●74

About the join: I should have clarified that I've simplified the WHERE conditions for the sake of isolating the problem. There are several other fields I use, but didn't need to reproduce the problem.

Others have told me that this is supported in some databases. I haven't tried to reproduce this in MySQL personally, but the answer is just "I can't do this in Advantage" then?

(15 Jan, 13:35) Eric G

Stephan has a good point about the use of OUTER JOIN in this case. I also think that INNER JOIN should be used. If OUTER JOIN is used, an additional check for "FileUsers.UserType IS NOT NULL" should be in the WHERE clause to eliminate the non-matches. That is just an extra, unnecessary step that is taken care of with INNER JOIN. But as you said, there are other conditions in the original statement so there may be other reason for the OUTER JOIN.

(15 Jan, 13:55) Alex Wong

Well, in real life, the WHERE clause is something like WHERE DocAccess.Public = TRUE OR FileUsers.UserNo = 2000. There are a couple of supported permission schemes that checks for values that may or may not exist in one or the other of the joined tables. When I simplified, I didn't keep the most sensible WHERE conditions. :-/

(15 Jan, 15:54) Eric G

Advantage Database Forum

In an EXISTS can my JOIN ON use a value from the original select?

Follow this question

Announcements

Related questions