CODEWITHSUNDEEP
sqlsql-servert-sql
Xavier W.
Xavier W.

Reputation: 1360

TSQL Select element with only one value

I have a project in which I need to save request for file. I have to save the state of the request with the date of each step.

I have two tables in my database :

FileRequest :

enter image description here

FileRequestStatus :

enter image description here

I would like to create a request to get each FileName from the table FileRequest having only the Status == 'NEW'. The desired output would be in this case C12345, LIVE.

If you have a better idea in order to build my database, I'll take it

I tried something like that : 

SELECT [FileName] FROM [FileRequest]
INNER JOIN [FileRequestStatus] ON [FileRequestStatus].[RequestId] = [FileRequest].[RequestId]
GROUP BY [FileRequestStatus].[RequestId]
HAVING COUNT([FileRequestStatus].[RequestStatus]) < 2

Upvotes: 0

Views: 64

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I think the simplest method is aggregation:

select fr.FileName
from nep.FileRequest fr join
     nep.FileRequestStatus] frs
     on frs.RequestId = fr.RequestId
group by fr.FileName
having min(frs.RequestStatus) = max(frs.RequestStatus) and
       min(frs.RequestStatus) = 'New';

Note: This assumes that the request status is never NULL (although that is easy to take into account).

What this does is aggregate by file name and then check that the statuses for a given file are all equal (the first condition) and equal to 'New' (the second condition).

Upvotes: 1

Kim Hoang
Kim Hoang

Reputation: 1368

You need to include [FileName] to your GROUP BY, then you can select it in result

SELECT [nep].[FileRequest].[FileName] FROM [nep].[FileRequest]
INNER JOIN [nep].[FileRequestStatus] ON [nep].[FileRequestStatus].[RequestId] = [nep].[FileRequest].[RequestId]
GROUP BY [nep].[FileRequestStatus].[RequestId], [nep].[FileRequest].[FileName]
HAVING COUNT([nep].[FileRequestStatus].[RequestStatus]) < 2

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

SELECT FR.FileName 
FROM [FileRequest] FR
INNER JOIN [FileRequestStatus] FRS ON FRS.[RequestId] = FR.[RequestId]
GROUP BY FR.FileName 
HAVING COUNT(CASE WHEN FRS.RequestStatus <> 'New' THEN 1 END) = 0 --No statuses other than `NEW`
AND COUNT(CASE WHEN FRS.RequestStatus = 'New' THEN 1 END) >= 1 --Atleast one status `NEW`

Upvotes: 1

Related Questions