Skip to Content (Press Enter)
UserImage.jpg
v
316Views
12Comments
Issue with INNER and Left Join
Question

I am having the below query

SELECT {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments], {CLD}.[LastUpdateOn],min({Project}.[Number]),count({Project}.[Number])
 FROM {CLD}
 INNER JOIN {Project} ON  {Project}.[Id] = {CLDProjects}.[ProjectId] 
 INNER JOIN {CLDProjects} ON {CLD}.[Id] = {CLDProjects}.[CLDId]
 WHERE 
   (
     @IsJAXPM =1 or 
      EXISTS (SELECT 1 FROM {CLDParticipant} WHERE {CLDParticipant}.[CLDId] = {CLD}.[Id] AND {CLDParticipant}.[UserId] = @UserId) or
      EXISTS (SELECT 1 FROM {ProjectParticipantWidget} INNER JOIN {ProjectParticipant} ON {ProjectParticipantWidget}.[ProjectParticipantId] =  {ProjectParticipant}.[Id]
     WHERE {ProjectParticipant}.[ProjectId] = {Project}.[Id] AND    {ProjectParticipant}.[UserId] = @UserId)

)
GROUP BY {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments], {CLD}.[LastUpdateOn]

The issue is the Select is pulling all the CLD elements without respect to the Project, I am trying to select CLD's whose Project id = Project.Id. I tried both the joins but it keep pulling all the values

Below is the structure enter image description here

0
0
08 Jun 2016
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Hi v,

I'm not sure what you're trying to achieve, or why you think it doesn't work. You are linking all Projects that are contained in all CLDs via the CLDProjects link table. What limit do you expect there to be?

0
0
09 Jun 2016
Copy Link
UserImage.jpg
v
Kilian Hekhuis wrote:

Hello Kilian,

The issue is we have a widget called Client Level Document and user will be able to upload the document and can associate projects or participant with the each Client Level Document.
 
All works perfectly but what happens is the CLD is getting visible to all the tenants.
 

Hi v,

I'm not sure what you're trying to achieve, or why you think it doesn't work. You are linking all Projects that are contained in all CLDs via the CLDProjects link table. What limit do you expect there to be?
 
 
 
0
0
09 Jun 2016
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
You didn't mention tennants before. Is this a multi-tennant application?
0
0
09 Jun 2016
Copy Link
UserImage.jpg
v
Kilian Hekhuis wrote:

Yes it is multi tenant application. But I am not calling ny tenant ID in the Entities for CLD.The Entity diagram for my widget looks like

I am currently having table to show the list of document using below query

SELECT {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments], {CLD}.[LastUpdateOn],min({Project}.[Number]),count({Project}.[Number])
FROM {CLD}
LEFT JOIN {CLDProjects} ON {CLD}.[Id] = {CLDProjects}.[CLDId] 
LEFT JOIN {Project} ON {CLDProjects}.[ProjectId] = {Project}.[Id] 
WHERE 
(@IsJAXPM =1 or 
 EXISTS (SELECT 1 FROM {CLDParticipant} WHERE {CLDParticipant}.[CLDId] = {CLD}.[Id] AND {CLDParticipant}.[UserId] = @UserId)  or 
  EXISTS (SELECT 1 FROM {ProjectParticipantWidget} INNER JOIN {ProjectParticipant} ON {ProjectParticipantWidget}.[ProjectParticipantId] = {ProjectParticipant}.[Id]
         WHERE {ProjectParticipant}.[ProjectId] = {Project}.[Id] AND {ProjectParticipant}.[UserId] = @UserId)
 )

GROUP BY {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments], {CLD}.[LastUpdateOn]
ORDER BY {CLD}.[LastUpdateOn] DESC

So the above query pull all the CLD and  put in all tenants.I am new to Outsystems and this kind of multiple tenant appliaction, so kid of lil stuck.

You didn't mention tennants before. Is this a multi-tennant application?
 
 
0
0
09 Jun 2016
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Unfortunately I've never dealt with multi-tenancy before, so I can only give limited advise with the information you provided. Is it possible you attach an eSpace to look at? One which has the data model (or references it)?
0
0
09 Jun 2016
Copy Link
UserImage.jpg
v
Kilian Hekhuis wrote:

The application is huge, I am not sure If I can attach entire espace. Because the data model are in different espace and the application different espace.Is there any other way of doing this.

Unfortunately I've never dealt with multi-tenancy before, so I can only give limited advise with the information you provided. Is it possible you attach an eSpace to look at? One which has the data model (or references it)?
 
 
0
0
09 Jun 2016
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
If the eSpace is so huge you can't attach it, you have some serious architectural problems, but that's for another day... If you attach the data eSpace only, I can at least take a look at the tables, see if they're ok.
0
0
09 Jun 2016
Copy Link
UserImage.jpg
v
Kilian Hekhuis wrote:

The sad part even they are bigger. Has lots of entities and action items. :-(

I am kind of very stuck,being new to Outsystems and these kind of kind of multitenancy application.
If the eSpace is so huge you can't attach it, you have some serious architectural problems, but that's for another day... If you attach the data eSpace only, I can at least take a look at the tables, see if they're ok.
 
 
0
0
09 Jun 2016
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Hi v,

Perhaps you can create a new eSpace, and reference from that eSpace the Entitities from the large data eSpace that you need in the query? (And attach the new eSpace)
0
0
10 Jun 2016
Copy Link
UserImage.jpg
v
Kilian Hekhuis wrote:

Hello Kilian,

i am attaching the Espace with the Entities from Data Espace. There was one issue all the entities was not set as the Mulitenant. Which I changed and now I dont see any records returned by the query also I am not able to insert new one. It throws an error called Indexou of Range.

It would be great if you can guide me on what I am missing here.

Thanks


Hi v,

Perhaps you can create a new eSpace, and reference from that eSpace the Entitities from the large data eSpace that you need in the query? (And attach the new eSpace)
 
 
TESTApplication.oml
0
0
10 Jun 2016
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Hi v,

If not all applicable Entitities were set to Multi Tenant, it's of no surprise you got data returned for all tenants. Note that in the eSpace you attached, CLDParticipant is still not multi tenant, which means you can't limit participants to a single tenant.

"Index out of Range" is not a database error. That must come from the code you wrote, and should be easily debugable. I'd advise you to debug the application (switch on the Break On All Errors for easy pinpointing of the error location), and see what causes it.
0
0
10 Jun 2016
Copy Link
2016-11-21 23-23-05
Gonçalo Borrêga
Check this:
You're probably missing the inner join to CLDProjects on the ProjectParticipant subquery.
Add
INNER JOIN {CLDProjects} ON {ProjectParticipant}.[ProjectId] =  {CLDProjects}.[ProjectId])

on the second EXISTS join conditions, otherwise the second resultset will match the EXIST condition for every project the user is in, ignoring the other conditions over CLDProjects. Try the following:

SELECT {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments]
      , {CLD}.[LastUpdateOn],min({Project}.[Number])
      ,count({Project}.[Number])
 FROM {CLD}
     INNER JOIN {Project} ON  {Project}.[Id] = {CLDProjects}.[ProjectId] 
     INNER JOIN {CLDProjects} ON {CLD}.[Id] = {CLDProjects}.[CLDId]
 WHERE 
   (
     @IsJAXPM =1 
     or EXISTS (SELECT 1 
             FROM {CLDParticipant} 
             WHERE {CLDParticipant}.[CLDId] = {CLD}.[Id] 
               AND {CLDParticipant}.[UserId] = @UserId) 
     or EXISTS (SELECT 1 
                FROM {ProjectParticipantWidget} 
                    INNER JOIN {ProjectParticipant} ON {ProjectParticipantWidget}.[ProjectParticipantId] =  {ProjectParticipant}.[Id]

                    INNER JOIN {CLDProjects} ON {ProjectParticipant}.[ProjectId] =  {CLDProjects}.[ProjectId]

                WHERE {ProjectParticipant}.[ProjectId] = {Project}.[Id] 
                  AND {ProjectParticipant}.[UserId] = @UserId)
   )
 GROUP BY {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments], {CLD}.[LastUpdateOn]

Also.. make sure you're not passing @IsJAXPM as 1... otherwise it will definitely return all records. Let us know if that works. Otherwise, please extend the diagram to show the ProjectParticipant and ProjectParticipantWidget tables as well.

0
0
11 Jun 2016
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.