CODEWITHSUNDEEP
sql-server
Hami
Hami

Reputation: 345

Joining master and child with only the row count of child table

Master Table:

CREATE TABLE [dbo].[db_Chat](
    [ChatID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [int] NOT NULL,
    [MsgText] [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
    [DateCreated] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_db_Chat] PRIMARY KEY CLUSTERED 
(
    [ChatID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Child Table:

CREATE TABLE [dbo].[db_Chat_Read](
    [ChatReadID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [int] NOT NULL,
    [ChatID] [int] NOT NULL,
    [DateCreated] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_db_Chat_Read] PRIMARY KEY CLUSTERED 
(
    [ChatReadID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I want to join the two tables and show the number of rows of child table, if there is no row selected then it can show zero.

I know the following code works fine.

select c.ChatID, COUNT(*) as ACount  from db_chat c
left join db_Chat_Read r
on c.ChatID = r.ChatID
where c.ChatGroupID=2
GROUP BY c.ChatID

But I need something like this in a single SQL code, the following SQL code is invalid, but it is to show what result I want: I need full fields of master table with only Row Count of child table.

select c.*, COUNT(r.*)  from db_chat c
left join db_Chat_Read r
on c.ChatID = r.ChatID
where c.ChatGroupID=2

Thank you~!

Upvotes: 0

Views: 1483

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can do what you want with a subquery:

select c.*,
       (select COUNT(*) from db_Chat_Read r where c.ChatID = r.ChatId
       ) as ACount
from db_chat c
where c.ChatGroupID = 2;

However, I would recommend putting the columns in the group by.

EDIT:

You can also do this with an additional join:

select c.*, coalesce(ACount, 0) as ACount
from db_chat c left join
     (select r.ChatId, count(*) as ACount
      from db_Chat_Read r
      group by r.ChatId
     ) r
    on c.ChatID = r.ChatID
where c.ChatGroupID = 2;

Upvotes: 3

Related Questions