Joining many-to-one with MySQL-CodePudding

I'm currently having an issue with a MySQL query for a small project management CMS I'm developing. I have two tables; projects & projectInfo. The project table defines a primary key, date of creation (timestamp), projectType & creatorId (manager who initally created the project).

The projectInfo table contains a primary key, projectId (which links to the primary key of the projects table), userId (user who is modifying the project), updatedTimestamp, projectName & projectDescription.

The point is that a manager would be able to create a project, start it off with a projectName and projectDescription, then other users can change the projectName and projectDescription themselves, leaving a log of who created the project, and who has been editing the project at each step.

So there are many projectInfo records leading to the same project, and the most recent projectInfo record is the latest iteration for that project. I would like a query that lists all records from the projects table, but also contains the latest singular updatedTimestamp, projectName, projectDescription & userId.

projects

| id | timestamp           | projectType | creatorId |
| -- | ------------------- | ----------- | ------ |
| 1  | 2022-05-11 11:17:22 | Design      | 1      |
| 2  | 2022-05-12 11:17:22 | Production  | 2      |

projectInfo

| id | projectId | userId | updatedTimestamp    | projectName | projectDescription |
| -- | --------- | ------ | ------------------- | ----------- | ------------------ |
| 1  |  1        | 1      | 2022-05-11 11:17:22 | McNuggets   | Makes chicken nuggets      |
| 2  |  1        | 2      | 2022-05-12 11:17:22 | McNuggets   | Makes chicken nuggets and burgers|
| 3  | 2         | 1      | 2022-05-13 11:17:22 | An unrelated Project | Idk yet |

I have a query that I modified. However, some data, such as creatorId & timestamp, is missing. I only get the following fields; id, projectId, userId, updatedTimestamp, projectName & projectDescription.

SELECT p.*  
FROM projects AS a   
   JOIN projectInfo AS p
      ON p.id =
        ( SELECT pi.id
          FROM projectInfo AS pi
          WHERE pi.projectId = a.id
          ORDER BY pi.updatedTimestamp DESC
          LIMIT 1
        ) ;

Any help would be appreciated.

Thanks, Ryan.

CodePudding user response：

The fields you are missing are those in the projects table, you forgot to also select the fields of a at the start of your query.

CodePudding user response：

Suppose that your sql works just fine and you only needed the columns inside your project (as it seems like this is what you're looking for), you need to include in the SELECT statement the columns of that table (project) when querying, like the below:

SELECT p.*, a.*
FROM projects AS a   
   JOIN projectInfo AS p
      ON p.id =
        ( SELECT pi.id
          FROM projectInfo AS pi
          WHERE pi.projectId = a.id
          ORDER BY pi.updatedTimestamp DESC
          LIMIT 1
        ) ;