Selecting specific row inclusion based on code

Transact-SQL
1
Inv# code#
12345 31
12345 11
45678 31
23456 31
23456 31

I need to pull all listings like INV# 12345, not INV# 23456 or INV# 45678. All listings that have 31 in one of the CODE# and not 31 in the next. Excluding all entries that that have 31 in all or only have one entry with 31 or not 31.

2 
SELECT Inv#
FROM table_name
GROUP BY Inv#
HAVING SUM(CASE WHEN code# = 31 THEN 1 ELSE 0 END) >= 1 AND
    SUM(CASE WHEN code# <> 31 THEN 1 ELSE 0 END) >= 1
2 Likes
3

This pulls all of the desired result. Thank you so much!

4

You're welcome!