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:
I have the following query:
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 ( Why doesn't this work? In ADS 10. |
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. Yeah, not ideal but I can work with that. Thanks for confirming.
(15 Jan '13, 15:54)
Eric G
|
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?
should do the trick I think. 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, 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, 13:55)
Alex Wong
Well, in real life, the WHERE clause is something like
(15 Jan '13, 15:54)
Eric G
|