CODEWITHSUNDEEP
sqlsql-server
BenCr
BenCr

Reputation: 6052

Using SQL, select a single record from a table for each associated record in it's associated foreign key table

I'm trying to get one latest Approved risk assessments (it's a bit column) for each department in the department table.

I also need all the not Approved risk assessments for all departments but I think I'll probably do that as a separate query or union the two result sets.

Here's a simplified version of my schema for what it's worth.

Department to RiskAssement table diagram

Upvotes: 0

Views: 211

Answers (2)

Bob Vale
Bob Vale

Reputation: 18474

If you have SQL 2005 or higher you can use a CTE to achieve this

with cte as (
   select row_number() over (partition by DeptId, order by Submitted desc) as row,
   id,
   DeptId,
   RiskAssessment,
   Submitted,
   Approved
   from RiskAssesment where approved = 1
)

select
   id,
   deptid,
   RiskAssessment,
   Submitted,
   Approved
   from cte where row = 1

Upvotes: 1

rahul maindargi
rahul maindargi

Reputation: 5635

To get the latest Approved task you will need Approval time Stored into column in RiskAssessment then you can use Group by and max to get the expected results.

For second part

all the not Approved risk assessments for all departments i think it wil be easier no need to be subquery. 

Select DEPTID, id where Approved=0

if this is not what you mean, I think i didnt understand your question well.

Upvotes: 0

Related Questions