CODEWITHSUNDEEP
sqlsql-server-2008
DotnetDude
DotnetDude

Reputation: 11807

SQL Query to get IDs of rows based on another column

As an example, if I have a table:

DECLARE @tmpTable TABLE
( SectionID INT,
  DetailID INT)

I have the following data:

SectionID    DetailID
1000          -1
1000           0
1000          -1
1100          5000
1100          0
1200          0
1200          0
1300          7000
1300          8000

How do I write the SQL to get all Section Ids that have ALL Detail IDs of 0 or -1. In the above example, the SQL should return 1000 and 1200 (not 1100 as atleast 1 item is not a 0 or a -1)

Upvotes: 0

Views: 153

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT DISTINCT SectionID
FROM @tmpTable AS t
WHERE NOT EXISTS
      ( SELECT *
        FROM @tmpTable AS tt
        WHERE tt.SectionID = t.SectionID
          AND tt.DetailID NOT IN (-1, 0)
      )

I think this should work too, since DetailID is an integer and the values you are checking (-1 and 0) have no gaps between them. And it could be faster, with an index on (SectionID, DetailID):

SELECT SectionID
FROM @tmpTable AS t
GROUP BY SectionID
HAVING MAX(DetailID) <= 0
   AND MIN(DetailID) >= -1

Upvotes: 3

Randy
Randy

Reputation: 16677

select sectionId from mytable where detailId in (0,-1)
and section id not in (select sectionid from mytable where detail_id > 0)

Upvotes: 0

Related Questions