SQLTeam.com | Weblogs | Forums

Select Statement help


#1

Have the following Data.
table name: sfdtlfil
ord_no--------oper_no
1234 10
1234 20
1234 30
3333 10
3333 20
4444 10
4444 20
4444 30
5555 10

Trying to get a select statement that will give me a list of order numbers that do not have an operation 30. I would expect it to return.

3333
5555


#2
SELECT ord_no
FROM
	sfdtlfil
GROUP BY
	ord_no
HAVING
	SUM(CASE WHEN oper_no=30 THEN 1 ELSE 0 END) = 0

or, even this

SELECT  DISTINCT ord_no
FROM    sfdtlfil t1
WHERE   NOT EXISTS 
		( 
			SELECT *
			 FROM   sfdtlfil t2
			 WHERE  t2.ord_no = t1.ord_no
					AND t2.oper_no = 30 
		)