Excluding records from SQL Query

Question

I have a table which I am trying to return only records of rendition_type Interactive and no other associated rendition_type. If a records has more than one rendition_type, I need to exclude it from the list.

DOC_ID    RENDITION_ID    RENDITION_TYPE
001       001             Interactive
001       002             Web PDF
002       003             Interactive
003       004             Interactive
003       005             Print PDF
004       006             Interactive
005       007             Interactive
005       008             Web PDF

I'm looking for a query that will return only rows where document_ID has Rendition_type type of only 1 and that type is Interactive.

Answer 1

This seems to give you what you want.

--demo setup
DROP TABLE IF EXISTS DocumentTable;
go
CREATE TABLE DocumentTable
    (DOC_ID int, RENDITION_ID int, RENDITION_TYPE varchar(11))
;

INSERT INTO DocumentTable
    (DOC_ID, RENDITION_ID, RENDITION_TYPE)
VALUES
    (001, 001, 'Interactive'),
    (001, 002, 'Web PDF'),
    (002, 003, 'Interactive'),
    (003, 004, 'Interactive'),
    (003, 005, 'Print PDF'),
    (004, 006, 'Interactive'),
    (005, 007, 'Interactive'),
    (005, 008, 'Web PDF')
;

--solution
SELECT *
FROM DocumentTable a
WHERE RENDITION_TYPE = 'Interactive'
    AND NOT EXISTS (
        SELECT *
        FROM DocumentTable
        WHERE DOC_ID = a.doc_id
            AND RENDITION_TYPE <> 'Interactive'
        )

---

| DOC_ID | RENDITION_ID | RENDITION_TYPE |
|--------|--------------|----------------|
| 2      | 3            | Interactive    |
| 4      | 6            | Interactive    |