Simple Query Left Join

  

Hello,

I'm getting a big problem on our applications design.
All entities will have access based on a simples user acl for each entity.

We will always display all the records from a entity, but on the websecreen we will scramble some information based on my access and allowing to request access.

The result expected is:



The problem is for that i need to do a left join with 2 conditions

Contact.Id = ContactACL.ContactId AND ContactACL.UserId = @UserId

Because i cant do a multiple condition on a simple query i'm forced to use advanced queries on all my application.


Anyone has another solution for avoiding all being done by advanced queries?

Hint: one ACL table that has (nº Contacts) * (nº Users) in out case will a table with more than 30 million of records.





Hi Vilhena,

The simpliest way is definitelly with an advanced query.
Another option would be using a webblock on the column "Contact Name", that would check if the user has access or not to the contact.

Cheers,

RG


Rúben Gonçalves wrote:
Hi Vilhena,

The simpliest way is definitelly with an advanced query.
Another option would be using a webblock on the column "Contact Name", that would check if the user has access or not to the contact.

Cheers,

RG

 
 
 Tnks Ruben,

Indeed we do not have other better solutions.

Is there any technical limitation known for the platform not supporting more than one condition on the join?


Hi Vilhena,

I think that the idea, is to keep Simple Queries simple. :)

Cheers,

RG
Rúben Gonçalves wrote:
Hi Vilhena,

I think that the idea, is to keep Simple Queries simple. :)

Cheers,

RG
I agree with you, the "simple queries" should be simple, and we always ensure that firstly use the "simple query" and that "advanced queries" are only for very specific scenarios. Our experience is that most of the bugs and problems arise from the use of advanced queries.
I do not see more complexity to add another condition in the query, unless other DBMS does not support it.
In my opinion the "simple query" could support it and could also support the "IN" that prevented developers to often apply the Distinct improperly.

Cheers,
Gonçalo Vilhena