April 27, 2011 at 8:28 pm
Hi,
I am using SQL 2008 R2 x64. I have a table lets say called "ParentTable" and a "ChildTable" where you can create them using the script below.
I need to write a query where it retrieves all parent fields as well as count of child records for a given parent.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ParentTable')
BEGIN
CREATE TABLE [dbo].[ParentTable]
(
[ridParentTable] INT IDENTITY(1,1) NOT NULL,
[ParentName] VARCHAR(100) NOT NULL,
[Address1] VARCHAR(100) NOT NULL,
[Address2] VARCHAR(100) NOT NULL,
CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED
(
[ridParentTable] ASC
)
);
CREATE UNIQUE NONCLUSTERED INDEX [UX_ParentTable_ParentName] ON [dbo].[ParentTable]
(
[ParentName]
);
END
GO
--
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ChildTable')
BEGIN
CREATE TABLE [dbo].[ChildTable]
(
[ridChildTable] INT IDENTITY(1,1) NOT NULL,
[keyParentTable] INT NOT NULL,
[ChildName] VARCHAR(100) NOT NULL,
CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED
(
[ridChildTable] ASC
)
);
ALTER TABLE [dbo].[ChildTable] ADD CONSTRAINT [FK_ChildTable_ParentTable] FOREIGN KEY
(
[keyParentTable]
) REFERENCES [dbo].[ParentTable]
(
[ridParentTable]
);
CREATE UNIQUE NONCLUSTERED INDEX [UX_ChildTable_keyParentTable_ChildName] ON [dbo].[ChildTable]
(
[keyParentTable],
[ChildName]
);
END
GO
--
INSERT INTO [ParentTable] ([ParentName], [Address1], [Address2]) VALUES ('Parent 1', 'Address 1', ' Address 2');
INSERT INTO [ParentTable] ([ParentName], [Address1], [Address2]) VALUES ('Parent 2', 'Address 1', ' Address 2');
INSERT INTO [ParentTable] ([ParentName], [Address1], [Address2]) VALUES ('Parent 3', 'Address 1', ' Address 2');
--
INSERT INTO [ChildTable] ([keyParentTable], [ChildName]) VALUES (1, 'Child 1');
INSERT INTO [ChildTable] ([keyParentTable], [ChildName]) VALUES (1, 'Child 2');
INSERT INTO [ChildTable] ([keyParentTable], [ChildName]) VALUES (1, 'Child 3');
INSERT INTO [ChildTable] ([keyParentTable], [ChildName]) VALUES (2, 'Child 1');
INSERT INTO [ChildTable] ([keyParentTable], [ChildName]) VALUES (2, 'Child 2');
INSERT INTO [ChildTable] ([keyParentTable], [ChildName]) VALUES (3, 'Child 1');
Result I am looking for is,
ridParentTable ParentName Address1 Address2 ChildCount
-------------- ---------- --------- --------- ----------
1 Parent 1 Address 1 Address 2 3
2 Parent 2 Address 1 Address 2 2
3 Parent 3 Address 1 Address 2 1
How do I write this query most efficiently. I thought about using GROUP BY but then I can only include column on which I use grouping!
Any help is appreciated.
Cheers 🙂
WRACK
CodeLake
April 27, 2011 at 8:36 pm
Seems to be a normal GROUP BY solution to me;
Here it is:
SELECT PT.ridParentTable , PT.ParentName , PT.Address1 , PT.Address2,
COUNT(CT.ChildName) Child_Count
FROM ParentTable PT
JOIN ChildTable CT
ON PT.ridParentTable = CT.keyParentTable
GROUP BY
PT.ridParentTable , PT.ParentName , PT.Address1 , PT.Address2
April 27, 2011 at 8:56 pm
Glad i could help 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply