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~!
No comments:
Post a Comment