CODEWITHSUNDEEP
sqlsql-server
NetProjects Ir
NetProjects Ir

Reputation: 61

selecting records from main table and count of each row in another table

I have 2 table in my database that tables are in relationship with foreign key I want to select all records from main table and then select count of each row in another table than have same ID from main table I tried to create a select query but it is not work correctly this query return all records from main table + count of all records from next table(not count of each row in relationship)

    SELECT tblForumSubGroups_1.id, tblForumSubGroups_1.GroupID,
    tblForumSubGroups_1.SubGroupTitle, tblForumSubGroups_1.SubGroupDesc,
    (SELECT COUNT(dbo.tblForumPosts.id) AS Expr1
    FROM dbo.tblForumSubGroups INNER JOIN dbo.tblForumPosts ON 
    dbo.tblForumSubGroups.id = dbo.tblForumPosts.SubGroupID) AS Expr1
    FROM dbo.tblForumSubGroups AS tblForumSubGroups_1 INNER JOIN
    dbo.tblForumPosts AS tblForumPosts_1 ON tblForumSubGroups_1.id 
    = tblForumPosts_1.SubGroupID

Upvotes: 1

Views: 933

Answers (4)

websch01ar
websch01ar

Reputation: 2123

Just to supply another answer though I believe the cross apply is likely the best option:

SELECT 
    A.id, A.GroupID, A.SubGroupTitle, A.SubGroupDesc,
    B.IDCount AS Expr1
FROM dbo.tblForumSubGroups A 
INNER JOIN (
    Select SubGroupID, Count(ID) as IDCount
    from dbo.tblForumPosts
    Group By SubGroupID
) B On A.ID = B.SubGroupID

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Do not mix sub-query and join logic. Use only one of them. I prefer sub-select.

SELECT tblForumSubGroups_1.id,
       tblForumSubGroups_1.GroupID,
       tblForumSubGroups_1.SubGroupTitle,
       tblForumSubGroups_1.SubGroupDesc,
       (SELECT COUNT(*)
        FROM dbo.tblForumPosts
        WHERE dbo.tblForumSubGroups.id = dbo.tblForumPosts.SubGroupID) AS Expr1
FROM dbo.tblForumSubGroups AS tblForumSubGroups_1

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

I would suggest cross apply as you can do a lot more things with it ...

SELECT t1.id,
   t1.GroupID,
   t1.SubGroupTitle,
   t1.SubGroupDesc,
   t2.val
FROM dbo.tblForumSubGroups AS t1
cross apply (SELECT COUNT(*)
         FROM dbo.tblForumPosts as t2
         WHERE t1.id = t2.SubGroupID) x(val)

Upvotes: 0

Vishal Gajjar
Vishal Gajjar

Reputation: 1019

SELECT  tblForumSubGroups_1.id, tblForumSubGroups_1.GroupID, tblForumSubGroups_1.SubGroupTitle, tblForumSubGroups_1.SubGroupDesc,
        COUNT(tblForumPosts_1.id) AS Expr1
FROM    dbo.tblForumSubGroups AS tblForumSubGroups_1
INNER JOIN  dbo.tblForumPosts AS tblForumPosts_1 ON tblForumSubGroups_1.id = tblForumPosts_1.SubGroupID
GROUP BY tblForumSubGroups_1.id, tblForumSubGroups_1.GroupID, tblForumSubGroups_1.SubGroupTitle, tblForumSubGroups_1.SubGroupDesc

Upvotes: 2

Related Questions