Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am attempting to determine/calculate the number of attachments associated with this component record but if there are no Components then I get a single record back with the AttachmentCount = 0 and what I really want is to not get any records if there are no componenta but if there are attachments the AttachmentCount field should reflect the count.
Is what I am trying to do possible or is there another way of doing this?

SQL
SELECT Component.*, Category.[Title] as CategoryStr, Subcategory.[Title] as SubCategoryStr, 
                    COUNT(*) as AttachmentCount FROM Component 
                   JOIN Category ON Component.[CategoryId] = Category.ObjectId
                   JOIN Subcategory ON Component.[SubCategoryId] = Subcategory.ObjectId                   
                   JOIN Attachment on Component.[ObjectId] = Attachment.[ParentId]


Thanks
Posted
Updated 8-Jan-14 5:28am
v3
Comments
Maciej Los 8-Jan-14 11:32am    
Mike, why do you use COUNT(*) when you join each other 4 tables? Use COUNT(Attachment.ParentID) instead.
Mike Hankey 8-Jan-14 11:39am    
It still returns a single record with the AttachmentCount = 0. What I am wanting is for the query to not return anything if there are no Components.
Maciej Los 8-Jan-14 11:40am    
Please, see my answer ;)

1 solution

Try this:
SQL
SELECT Com.*, Cat.[Title] as CategoryStr, Sct.[Title] as SubCategoryStr, (SELECT COUNT(*) FROM Attachment WHERE ParentId = Com.ObjectID) as AttachmentCount
FROM Component AS Com LEFT JOIN Category AS Cat ON Com.[CategoryId] = Cat.ObjectId
    LEFT JOIN Subcategory AS Sct ON Com.[SubCategoryId] = Sct.ObjectId


[EDIT]
If you want to get data only if component exists, you need to change Join direction ;)
Have a look here: Visual Representation of SQL Joins[^]
[/EDIT]

For further information, please see:
SQLite - Sub Queries[^]
SQLite - ALIAS Syntax[^]
 
Share this answer
 
v4
Comments
Mike Hankey 8-Jan-14 11:48am    
Maciej it worked great but had to modify your solution

SELECT Com.*, Cat.[Title] as CategoryStr, Sct.[Title] as SubCategoryStr,
(SELECT COUNT(*) FROM Attachment WHERE ParentId = Com.ObjectID) as AttachmentCount
FROM Component AS Com
JOIN Category AS Cat ON Com.[CategoryId] = Cat.ObjectId
JOIN Subcategory AS Sct ON Com.[SubCategoryId] = Sct.ObjectId

Thanks for the help
Maciej Los 8-Jan-14 11:49am    
You're welcome. Please see updated answer. There is another tip!
;)
Mike Hankey 8-Jan-14 11:56am    
It works either way with or without the LEFT keyword.

Thanks again
thatraja 8-Jan-14 12:25pm    
5!
Maciej Los 8-Jan-14 12:35pm    
Thank you, Raja ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900