CODEWITHSUNDEEP
sqlsql-server
morkel flores
morkel flores

Reputation: 36

How do I build this SQL query

I have this table tblFSIC which consist of Id, BIN , SerialNumber, and year

I want to check if there already a SerialNumber on other BIN:

The query I use was:

SELECT * 
FROM tblFSIC 
WHERE ControlNumber = @ControlNumber AND NOT BIN = @BIN;

But I also need to check if the BIN and Year has duplicate, and my query for that:

SELECT * 
FROM tblFSIC 
WHERE BIN = @BIN AND Year = @Year;

The first think of using:

 SELECT * 
 FROM tblFSIC 
 WHERE (ControlNumber = @ControlNumber AND NOT BIN = @BIN) 
    OR (BIN = @BIN AND Year = @Year);

But it will just return any ControlNumber that has different BIN;

I have no idea on how do I fit 2 different conditions on query

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you want aggregation:

SELECT SerialNumber
FROM tblFSIC 
GROUP BY SerialNumber
HAVING SUM(CASE WHEN ControlNumber = @ControlNumber AND NOT BIN = @BIN THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN BIN = @BIN AND Year = @Yea THEN 1 ELSE 0 END) > 0;

This finds serial numbers that meet both conditions. Use = 0 in the having if you want serial numbers that fail both conditions.

Upvotes: 1

Related Questions