CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel Santos
Daniel Santos

Reputation: 188

Find rows matching a value and not matching another

I did a search on the board before I posted this but I could not find anything that covers the same scenario, although I believe it is here buried somewhere I could not see it...

I'm learning SQL and trying to build a query that returns the column IndexNr when IndexNr row contains the ID = x but IndexNr does not contain any row with ID = y

I not looking for any particular IndexNr... I want all of them that has ID = x but the same IndexNr that returns a match for ID = x cannot be considered a match if it also has ID = y in one of the rows from Pos column... Does that makes sense?

IndexNr     Pos    ID
----------- ------ -----------
100001      0      -30140
100001      1      -28877
100001      2      -31659
100001      3      -28282
100003      0      -30262
100003      1      -30261
100003      2      -30260

So I tried this, but of course my limited SQL skills are keeping me from seeing the further clauses I'm missing to achieve this outcome...

SELECT [ToolNr] FROM [WTData].[dbo].[ToolParts] 
WHERE PartID = -30140 AND PartID <> -28877
ORDER BY [ToolNr]

From the query above, the IndexNr 100001 should not be returned as a match...

Thanks in advance!

Upvotes: 0

Views: 1236

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You may look for NOT EXISTS and a correlated subquery, if I understood you right.

SELECT DISTINCT
       t1.indexnr
       FROM elbat t1
       WHERE t1.id = <your x>
             AND NOT EXISTS (SELECT *
                                    FROM elbat t2
                                    WHERE t2.indexnr = t1.indexnr
                                          AND t2.id = <your y>);

If (indexnr, id) is unique, you don't need the DISTINCT.

An alternative could be aggregation. Assuming that your x < your y:

SELECT indexnr
       FROM elbat
       WHERE id IN (<your x>, <your y>)
       GROUP BY indexnr
       HAVING max(id) = min(id)
              AND max(id) = <your x>;

It first gets only records where the id is either your x or your y. Then these are grouped by indexnr. Now, for an indexnr, if all of those previously filtered rows contain either only y or only x in id, max(id) must be equal to min(id). Since we want only x, we can check if max(id) (or min(id)) is x.

Upvotes: 3

Related Questions