Issue understanding the Inner Join Or Left join

Question

I am having an issue understanding the inner and left join

I am having the below query in the outsystems

 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 how the structure looks like

Your join conditions look a bit scrambled, I didn't even know you could reference tables from latter joins. (...and what is with the use of `{` and `}`?) — Uueerdo, Jun 08 '16 at 16:21
You can reference tables from later joins (because SQL mixes the joins together to optimize), but I agree it's really hard to read like this. The CLDProjects should definitely be before Project for readability. As for the { }, they allow the OutSystems Platform to expand into those virtual table names, the effective physical table name it ended up in the database. — Miguel Seabra Melo, Jun 09 '16 at 04:57
Those whom the gods would destroy they first make use development tools with vendor-specific "enhancements"... — Bob Jarvis - Слава Україні, Jun 10 '16 at 16:48

score 2 · Accepted Answer · answered Jun 08 '16 at 16:22

2

Please try the following: First get the CLDProjects matching with Project then get the CLD from matched records.

FROM {CLD} INNER JOIN (
    {CLDProjects} INNER JOIN {Project} ON  {Project}.[Id] = {CLDProjects}.[ProjectId]
  ) ON {CLD}.[Id] = {CLDProjects}.[CLDId]

answered Jun 08 '16 at 16:22

smozgur

1,772
1
15
23

Smozgur, I tried the above join but still its pulling everything – trx Jun 08 '16 at 16:33
Then I would try to remove WHERE part to test to see what it returns. After INNER JOIN above it shouldn't matter however I just noticed ORs in your WHERE. Make sure WHERE doesn't cause that. Weird, above INNER JOIN makes sure that source recordset contains CLDs matching with the CLDProjects which contains matching Projects only. – smozgur Jun 08 '16 at 16:37

score 1 · Answer 2 · answered Jun 10 '16 at 16:42

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 it will the second results will match the exists 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.

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

I think you just need to reorder your joins, I tried to reproduce your case (with incorrect joins order), but it gives me error when test the query,

but if you reorder the the joins, it works. note that I am using OutSystems 10

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 09 '16 at 01:34

Mudallal

53
8

Issue understanding the Inner Join Or Left join

3 Answers3