Select fields from different tables

Select fields from different tables

  
I am trying to use Advanced SQL queries to select the different tables, with maximum of the LastUpdatedOn fields from Question Document and Instruction tables

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],
(
SELECT MAX(LastUpdatedOn) AS max_LastUpdatedOn
FROM
(
    SELECT LastUpdatedOn FROM {Question}
    UNION ALL
    SELECT LastUpdatedOn FROM {Document}
    UNION ALL
    SELECT LastUpdatedOn FROM {Instruction}
) A
)[max_LastUpdatedOn]

From {Project}

INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON {ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {InstructionType} ON {Project}.[Id] = {Instruction}.[ProjectId]
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]

GROUP BY {Project}.[Number],
         {Project}.[Name],
         {User}.[Last_Login]
         
ORDER BY {Project}.[Number]

But this is throwing an error
Error in advanced query GetProjectByUserId in Preparation in ProjectPermissionAccess in AuditReports in BreedingServicesMng (SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],  (  SELECT MAX(LastUpdatedOn) AS max_LastUpdatedOn  FROM   (      SELECT LastUpdatedOn FROM {Question}      UNION ALL      SELECT LastUpdatedOn FROM {Document}      UNION ALL      SELECT LastUpdatedOn FROM {Instruction}  ) A  )[max_LastUpdatedOn]    From {Project}    INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]  INNER JOIN {User} ON {ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId  INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]  INNER JOIN {InstructionType} ON {Project}.[Id] = {Instruction}.[ProjectId]  INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]    GROUP BY {Project}.[Number],           {Project}.[Name],           {User}.[Last_Login]             ORDER BY {Project}.[Number]  ): The multi-part identifier "PORTAL_BREEDING_SERVICES.DBO.OSUSR_E2R_INSTRUCTION1_T9.PROJECTID" could not be bound.

Please help me with this. Thanks
ok,

I am not sure where the problem lies, but just start by making it simpler.
It seems Instruction doesn't have a projectid, or it's different called
it could be aything in the sql.

1. use parentheses in the joins, for example:
({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
2. start simple, test the query and continue to expand it.
3. rethink the max-field..imho it could be better


I tried to run the query without selecting the max of LastUpdatedOn section. It works. But I am not sure how do I select the Maximum of LastUpdatedOn from the Document,Instruction,Qusetion for the particular Project Id.

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],
(
SELECT MAX(LastUpdatedOn) AS max_LastUpdatedOn
FROM
(
    SELECT {Question}.[LastUpdatedOn]
    UNION ALL
    SELECT {Document}.[LastUpdatedOn]
    UNION ALL
    SELECT {Instruction}.[LastUpdatedOn]
) A
)[max_LastUpdatedOn]

From {Project}

INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]

GROUP BY {Project}.[Number],
         {Project}.[Name],
         {User}.[Last_Login],
               
ORDER BY {Project}.[Number]

I am getting the below error
Column 'PORTAL.OSUSR_E2R_QUESTIONS_T9.LASTUPDATEDON' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
well, why not make it 3 columns, and just max all 3. figure out the real max date later on?

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login], MAX({Question}.[LastUpdatedOn])
, MAX({Document}.[LastUpdatedOn]), MAX({Instruction}.[LastUpdatedOn])

I am sorry I dont get you. But I tried to use already just SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],MAX({Question}.[LastUpdatedOn],{Instruction}.[LastUpdatedOn],{Document}.[LastUpdatedOn])

it was giving me The MAX function requires 1 argument(s)
no, add 2 attributes in your structure.

so you have Max(z), Max(y), max(z)

then in your webscreen for exampel you do the choice which date you want to represent