quote:

---

Originally posted by GC72

Hi,

I have three tables where ENTRY table (EntryID) is for the main records, EntryAttribute table (EntryID, AttributeID)is populated when an AttributeDefinitionID is associated with the Entry and the actual value for the attribute is stored in AttributeString table (AttributeID, AttributeDefinitionID, Value).

An EntryID can have multiple AttributeID for the Language attribute where the attributedefinitionid=5. But I'd like to return distinct EntryIDs that have only 1 attributeID for attributedefinition=44

Hope this makes sense. This is my query where I am querying for a count of AttributeIDs per distinct EntryID where the AttributeDefinitionID=5 So that I can see all the EntryIDs that have a count of 1. However, the numbers doesnt seem right.

Select distinct a.entryID,
count(distinct b.attributeID) as Language
from entry as a
inner join EntryAttribute as b on b.entryID = a.entryID
inner join AttributeString as c on c.AttributeID = c.AttributeID
where c.AttributeDefinitionID =5
GROUP BY A.ENTRYid

How can I do this by using Having Count, to return total number of distinct ENTRYIDs with 1 AttributeID for the Language attribute (AttributedefinitonID=5)

Thanks
GC

---

SELECT DISTINCT
        a.entryID ,
        COUNT(DISTINCT b.attributeID) AS Language
FROM    entry AS a
        INNER JOIN EntryAttribute AS b ON b.entryID = a.entryID
        INNER JOIN AttributeString AS c ON c.AttributeID = c.AttributeID
WHERE   c.AttributeDefinitionID = 5
GROUP BY A.ENTRYid[red]
HAVING COUNT(DISTINCT b.attributeID) = 1