Try this:
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[
^]