CODEWITHSUNDEEP
mysqlsql
bmichotte
bmichotte

Reputation: 610

Join query by date and primary

I have the followings tables on my database

database tables

There are several project statuses (waiting, working, finished, paid) and a project have to pass through all the status from waiting to paid.

In order to keep complete track of the project, each status are kept, so we know when a project changed its status.

I would like to create a search filter with the current status (the one with the more recent "created_at").

I tried few queries in order to get the correct result, but I'm stuck with this one.

The not-working query I have is

select p.* from projects p
left join projects_status_projects psp on psp.project_id = p.id
where p.id in (select project_id from projects_status_projects 
      where project_status_id = XX group by project_id order by created_at desc)

Ofc, this query does not work and return me each projects with a record for project_status_id XX without taking in account the order.

Any hint would be very appreciated !

Thanks,
Benjamin

Upvotes: 1

Views: 81

Answers (4)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Here is one way of solving it:

select p.* from projects p
left join projects_status_projects psp on psp.project_id = p.id
where p.id in (select project_id from projects_status_projects 
      where project_status_id = XX and created_at = min(created_at) group by project_id)

Upvotes: 0

guido
guido

Reputation: 19214

select p.* from projects p
left join projects_status_projects psp on (psp.project_id = p.id)
where created_at = (select max(created_at) 
                    from projects_status_projects where
                    project_id = p.id)

sqlfiddle with some example here: http://sqlfiddle.com/#!2/725fcc/7/0

Upvotes: 1

Gaurav Singla
Gaurav Singla

Reputation: 1451

try this:

 select p.* from projects p
    left join projects_status_projects psp on psp.project_id = p.id
    where psp.id = (select max(id) from projects_status_projects 
          where projects_status_projects.project_id =p.id ) and psp.project_status_id = XX

or 

select p.* from projects p,projects_status_projects psp
    where psp.project_id = p.id and psp.id = (select max(id) from projects_status_projects 
          where projects_status_projects.project_id =p.id ) and psp.project_status_id = XX

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

If you just need the current status of the project

Try this::

Select DITINCT(p.id),p.* 
from projects p
left join projects_status_projects psp on psp.project_id = p.id
where project_status_id = XX 
order by created_at desc

Upvotes: 1

Related Questions