theunknownknight
MIS
Making an ASP.NET page and I am experiencing the following difficulty
I am working on a project that requires 4 seperate queries. One of the queries has to do the following 3 things.
1. Need all projects from Project table that were initiated by the current user (all projs from table where login.login = 'randomlogname'
2. From the above returned records I need those that occur less than the count-1 of the login table. In other words, when everyone else review project, it should not be returned. FYI when other persons view another project it is recorded in the projectreview table
These two are working correctly: Here is the correct SQL -
SELECT DevProject.ProjNum, DevProject.Name
FROM Login INNER JOIN DevProject ON Login.PKey = DevProject.OwnerLogin
WHERE (((DevProject.ProjNum) Not In (SELECT t1.projnum FROM [select Projectreview.projnum, Projectreview.Upkey, Count(Projectreview.login ) as LC from Projectreview group by Projectreview.projnum, Projectreview.Upkey]. AS t1 INNER JOIN [Select count(login.pkey)-1 as LC1 from login]. AS t2 ON t1.LC = t2.LC1)) AND ((Login.Login)="jdyess"
);
The Problem comes when I get to the third step.
3. I need to add to the above returned records those where projectreview.UpKey <> DevProject.Upkey
I am trying to inner join another subquery to the sql statement that connect the projectreview table directly to the devtable via the upkey field to do this (instead of going through login). My issue is, how do I properly add another inner join with a subquery to the above query?
I am working on a project that requires 4 seperate queries. One of the queries has to do the following 3 things.
1. Need all projects from Project table that were initiated by the current user (all projs from table where login.login = 'randomlogname'
2. From the above returned records I need those that occur less than the count-1 of the login table. In other words, when everyone else review project, it should not be returned. FYI when other persons view another project it is recorded in the projectreview table
These two are working correctly: Here is the correct SQL -
SELECT DevProject.ProjNum, DevProject.Name
FROM Login INNER JOIN DevProject ON Login.PKey = DevProject.OwnerLogin
WHERE (((DevProject.ProjNum) Not In (SELECT t1.projnum FROM [select Projectreview.projnum, Projectreview.Upkey, Count(Projectreview.login ) as LC from Projectreview group by Projectreview.projnum, Projectreview.Upkey]. AS t1 INNER JOIN [Select count(login.pkey)-1 as LC1 from login]. AS t2 ON t1.LC = t2.LC1)) AND ((Login.Login)="jdyess"
);The Problem comes when I get to the third step.
3. I need to add to the above returned records those where projectreview.UpKey <> DevProject.Upkey
I am trying to inner join another subquery to the sql statement that connect the projectreview table directly to the devtable via the upkey field to do this (instead of going through login). My issue is, how do I properly add another inner join with a subquery to the above query?