SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HAVING COUNT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GC72
Starting Member

16 Posts

Posted - 07/05/2013 :  12:52:19  Show Profile  Reply with Quote
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

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/05/2013 :  12:58:58  Show Profile  Reply with Quote
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

I didn't follow the logic you are trying to implement. Nonetheless, don't you need a "attributedefinition=44" somewhere in that query?
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/05/2013 :  13:09:09  Show Profile  Reply with Quote
It should be attributedefiniton = 5 not 44(my mistake).

AttributeString table
AttributeID, AttributeDefinitionID, AttributeValue
01
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/05/2013 :  13:09:09  Show Profile  Reply with Quote
It should be attributedefiniton = 5 not 44(my mistake).

AttributeString table
AttributeID, AttributeDefinitionID, AttributeValue
01
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/05/2013 :  13:17:59  Show Profile  Reply with Quote
Is EntryAttribute to AttributeString table a 1-1 relationship, or can there be one to many? If it is 1-1, perhaps this?This is the same code that you posted except for adding a having clause at the end.
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
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/05/2013 :  13:21:52  Show Profile  Reply with Quote
It should be attributedefiniton = 5 not 44(my mistake).

AttributeString table
AttributeID, AttributeDefinitionID, AttributeValue
01 5 English
02 5 French
03 5 German
04 5 Italian
05 10 someothervalue not language

EntryAttribute table
EntryID, AttributeID
444 01
444 02
444 03
123 01
666 04
333 02
333 03

So I am interested in ENTRYIDs that have only 1 attribute assigned where attribute type is language and is defined by attributedefinitonid = 5

for exmaple EntryIDs 123, and ,666 should return as they have 1 attributeID

Hope this makes sense
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/05/2013 :  13:33:04  Show Profile  Reply with Quote
SELECT
	e.EntryId
FROM
	EntryAttribute e
	INNER JOIN AttributeString a ON 
		a.AttributeID = e.AttributeID AND a.AttributeDefinitionID = 5
GROUP BY
	e.EntryId
HAVING
	COUNT(DISTINCT e.AttributeId) = 1
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/08/2013 :  06:51:42  Show Profile  Reply with Quote
Thanks James. There can be one to many between EntryAttribute and AttributeString, e.g. one EntryID can have many AttributeIDs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/08/2013 :  10:51:09  Show Profile  Reply with Quote
Can you try the query that I posted earlier? Does it give incorrect results?
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/08/2013 :  12:13:55  Show Profile  Reply with Quote
It seems to give correct results. Many thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000