SQL SELECT DISTINCT Puzzle

Question

I'm trying to create a query from two tables.

The first table is 'Document Type' and the second one 'Document' Document Type is, if you like a category, and 'Document' contains details about the document itself. They are related (ie 'Document' contains a field which relates to the key field of 'Document Type').

Simplified, the tables look like this:-

Document Type
=============
TypeID
TypeName
TypeDescription


Document
========
DocumentID
DocumentType (fk of TypeID)
DocumentRequired

The query I'm running takes a DISTINCT list of Document types - this is the first and easiest bit.

What I'm wanting to do is to add a column to the query which then looks under 'Document' and, if there have ever been any related documents where 'DocumentRequired' is equal to TRUE, displays a TRUE/Yes value. If not, show FALSE/No.

I've tried creating a join but obviously if there are any categories which contain both required/non-required documents I get duplication. All I want is a yes/no flag.

Can somebody help?

Please show your existing code, even though it doesn't work. It will be much easier for someone to answer your question then. They can just fix it, rather than having to start from scratch. — dan1111, Oct 16 '12 at 14:54
What do you want the query to do in the case that you have both TRUE and FALSE? — Gidil, Oct 16 '12 at 14:56
If it finds true and false, then I want it to display true. The basic point of the final column is to say are there any documents in that category that have been marked as DocumentRequired = true. — Mat Richardson, Oct 16 '12 at 15:00

score 1 · Accepted Answer · answered Oct 16 '12 at 14:58

1

SELECT dt.TypeID,dt.TypeName,dt.TypeDescription,
    CASE
        WHEN sum(CONVERT(int,ISNULL(d.DocumentRequired,0)))=0 THEN 'False/No'
        ELSE 'True/Yes'
    END [Any required documents]
FROM DocumentType dt
LEFT JOIN Document d on dt.DocumentType=dt.TypeID --terrible naming convention....
group by dt.TypeID,dt.TypeName,dt.TypeDescription

answered Oct 16 '12 at 14:58

UnhandledExcepSean

12,504
2
35
51

INNER JOIN is wrong becuase this query will drop all document types for which there is no document – Tobsey Oct 16 '12 at 15:00

score 0 · Answer 2 · answered Oct 16 '12 at 14:59

Select DISTINCT TypeID, 
                TypeName, 
                TypeDescription, 
                CASE WHEN 
                    (select count(*) 
                            from Document 
                            where document.DocumentType = DocumentType.TypeID 
                            and DocumentRequired = 'TRUE'
                    )>0 
                    THEN 'YES' 
                    ELSE 'NO' 
                END AS myYesNoField 
    FROM DocumentType

score 0 · Answer 3 · answered Oct 16 '12 at 14:59

SELECT
    TypeID,
    TypeName,
    TypeDescription,
    CASE WHEN NumRequiredDocuments > 0 THEN 'Yes' ELSE 'No' END RequiredDocumentsExist
FROM
(
    SELECT
        DocumentType.TypeID,
        DocumentType.TypeName,
        DocumentType.TypeDescription,
        SUM (CASE WHEN Document.Required <> 0 THEN 1 ELSE 0 END) NumRequiredDocuments
    FROM
        DocumentType
        LEFT JOIN Document ON DocumentType.TypeID = Document.DocumentType
)
GROUP BY
    TypeID,
    TypeName,
    TypeDescription

score 0 · Answer 4 · answered Oct 16 '12 at 15:13

Given the following records in your tables: DocumentType

TypeID  TypeName    TypeDescription
1   Type1   1st Type
2   Type2   2nd Type
3   Type 3  3rd Type

and Document

DocumentId  DocumentType    DocumentRequired
1       1       0
2       1       1
3       2       0
4       3       1

Then the follwoing select will get you what you want:

SELECT  TypeID, 
        TypeName, 
        TypeDescription, 
        CASE 
            WHEN EXISTS 
                (SELECT * 
                FROM Document 
                WHERE Document.DocumentType = TypeID 
                    AND DocumentRequired = 1) THEN 'True'
            ELSE 'False'
        END AS DocumentRequired
FROM DocumentType

SQL SELECT DISTINCT Puzzle

4 Answers4

Linked