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.

asked 15 Jan '13, 10:32

Eric%20G's gravatar image

Eric G
3113821
accept rate: 100%


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.

link

answered 15 Jan '13, 13:46

Alex%20Wong's gravatar image

Alex Wong
5.1k2486
accept rate: 41%

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?

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, 13:26

Stephan%20Leiwering's gravatar image

Stephan Leiw...
1.8k52652
accept rate: 22%

edited 15 Jan '13, 13:48

Alex%20Wong's gravatar image

Alex Wong
5.1k2486

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 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 '13, 15:54) Eric G
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×247

Asked: 15 Jan '13, 10:32

Seen: 229 times

Last updated: 15 Jan '13, 15:54

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.