I think this is a good use case for EXCEPT :

SELECT bookNo 
FROM tableName
WHERE [BookParameter] = 1 AND [Value] = 'FN' 
EXCEPT
SELECT bookNo 
FROM tableName
WHERE [BookParameter] = 4 AND [Value] = 'Yes'

The result will contain only those rows fetched by the first query that do not occur in the result of the second query.

I like this option because I think it is really good readable and very simple.

You could argue that use a GROUP BY clause with conditional aggregation might be faster. But thanks to the hard restrictions in the WHERE clauses, I assume this will be insignificant in this case.

Read more about EXCEPT in the documentation .

Sample fiddle here

A combination of group by , sum and iif can help you get the expected results.

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE TableName (
    [BookNo] int, 
    [BookParameter] int, 
    [Value] varchar(5)
);
    
INSERT INTO TableName ([BookNo], [BookParameter], [Value]) VALUES
(1234, 1, 'FN'),
(1234, 2, 'False'),
(1234, 3, '30'),
(1234, 4, 'YES'),
(2222, 1, 'FN'),
(2222, 2, 'True'),
(2222, 3, '25'),
(2222, 4, 'NO'),
(3333, 1, 'THR'),
(3333, 2, 'True'),
(3333, 3, '54'),
(3333, 4, 'YES'),
(4444, 1, 'FN'),
(4444, 2, 'TRUE'),
(4444, 3, '12'),
(4444, 4, 'NO');

The query:

SELECT  bookNo 
FROM tableName
GROUP BY bookNo 
HAVING SUM(IIF(bookparameter = 1 AND value = 'FN', 1, 0)) = 1 
AND SUM(IIF(bookparameter = 4 AND value = 'YES', 1, 0)) = 0;

Using conditional aggregation allows me to make sure there's at least one record with 'FN' value for bookparameter 1 and no records with 'YES' value for bookparameter 4 for each value of bookNo .

Results:

bookNo
2222
4444

You can see a live demo on db<>fiddle

You need to select all the books that match bookparameter=1 and value='FN', then from the matched books exclude the books that have bookparameter=4 and value='YES'

select 
        bookno 
    from 
        books b
    where 
        b.bookparameter = 1 and b.value='FN' and
        not exists
        (
            select 
              1 
            from 
                books b2 
            where b.bookno = b2.bookno 
                  and b2.bookparameter=4 and b2.value='YES'
        )

SELECT T.BOOKNO
FROM YOUR_TABLE AS T
WHERE T.bookparameter = '1' and T.value = 'FN'
AND NOT EXISTS
(
  SELECT 1 FROM YOUR_TABLE AS X
  WHERE T.BOOKNO=X.BOOKNO AND X.bookparameter = '4' and T.value = 'YES'
)

Could you please try this query

Try this script

SELECT t1.BOOKNO FROM #books AS t1
LEFT JOIN (
  SELECT BookNo FROM #books WHERE BookParameter = '4' and Value = 'YES')t2 
  ON t1.BookNo = t2.BookNo 
WHERE t1.BookParameter = '1' AND t1.Value = 'FN' and t2.BookNo IS NULL