CODEWITHSUNDEEP
sqlsql-server
Mat Richardson
Mat Richardson

Reputation: 3606

SQL SELECT DISTINCT Puzzle

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?

Upvotes: 0

Views: 198

Answers (4)

Steve Ford
Steve Ford

Reputation: 7753

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

Upvotes: 0

Tobsey
Tobsey

Reputation: 3400

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

Upvotes: 0

Andrea Ligios
Andrea Ligios

Reputation: 50203

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

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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

Upvotes: 1

Related Questions