Retrieve column values from table based on condition in SQL Server

Question

I have a table with three columns:

(JobNo, ProgramId & Status)

JobNo have many ProgramId and each ProgramId has Status

I need to retrieve only those JobNo where all the ProgramId for the same JobNo have Status = "delivered".

From the given image only JobNo (1&4) should be output as only JobNo (1&4) the ProgramId's Status="delivered".

Click here to see table

You should edit your question and provide sample data and desired results. The explanation is dense and hard to follow. — Gordon Linoff
– Gordon Linoff, Commented Mar 19, 2016 at 15:27

Code Different · Accepted Answer · 2016-03-19 15:37:41Z

1

Try something like this (not tested):

SELECT      JobNo
FROM        MyTable
GROUP BY    JobNo
HAVING      SUM(IIF(Status = 'Delivered', 0 , 1)) = 0

answered Mar 19, 2016 at 15:37

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanku sir.Got what i wanted.

Gordon Linoff · Accepted Answer · 2016-03-19 15:33:50Z

1

Your question suggests group by with having:

select jobid
from t
group by jobid
having min(status) = max(status) and min(status) = 'delivered';

answered Mar 19, 2016 at 15:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Thanku sir.Got what i wanted.