I have the following table for document storage. The document could be either a book with multiple pages, page numbering starting from 0 or a singlepage document.
Category
Title
PageNo - integer
LineNo integer
Key
Value
I need a query that pulls all the keys from the table that belong to one-page documents and Category1.
I have the following query:
SELECT DISTINCT Key
FROM Materials
WHERE Category='Category1' and Key NOT IN
(SELECT DISTINCT Key
from Materials
WHERE Category='Category1'
and PageNo>0)
The query works fine excluding all the materials that have more than 1 page. I wanted to rewrite the query to improve it in terms of performance and also to avoid any repeating code such as keyword DISTINCT and WHERE clause, if possible. I'd appreciate any pointers in the right direction.
if PageNo is not null
SELECT Key
FROM Materials
WHERE Category='Category1'
GROUP BY Key
having max(PageNo) <= 0
if PageNo is nullable
SELECT Key
FROM Materials
WHERE Category='Category1'
GROUP BY Key
having max(isnull(PageNo,0)) <= 0
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments