How to get child table row count with master table columns in single query

Question

1.00/5 (2 votes)

See more:

I am using MS SQL Server 2012
DB has one master and one child table.
I need some columns from master with number of active(Status=1) and inactive(Status=0) child records count. Please refer sample data table given below

I got the result by writing stored procedure. But I am looking for a better query or CTE with good performance.
Approximate Row count in Master Table (Table A) : 500
Apporximate Row Count in Child Table (Table B) : 300000 and size is increasing

Table A
**************************

HTML

Col1 Col2 Col3
1    A1   A11
2    A2   A12
3    A3   A13

Table B
***************************

HTML

Col1 Col2 Col3 Status
1    1    B1   1
1    2    B2   1
2    3    B3   0
2    4    B4   1
3    5    B5   1

Result
**************************

HTML

A.Col1 A.Col2 ActiveCount InactiveCount
1      A1     2           0
2      A2     1           1
3      A3     1           0

Any help??..

Here is my existing query

SQL

SELECT T1.* , 
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='A') AS ActiveCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='R') AS InactiveCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='B') AS BlockedCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='C') AS CancelledCount,
			(SELECT COUNT (Col2 ) FROM B
			WHERE B.Col1 =T1.Col1 
			AND B.Status='D') AS DeactivatedCount
			FROM (
			SELECT  Col1 ,Col2 ,Col3 FROM A WHERE A.Col1  IN 
			(SELECT DISTINCT B.Col1 FROM B WHERE B.ColXX='TRUE')
			) AS  T1

Posted 23-Nov-15 2:38am

Sadique KT

Updated 23-Nov-15 22:52pm

v3

Add a Solution

Comments

F-ES Sitecore 23-Nov-15 8:45am

How do we know if something is a better query if we don't know what your existing query is? You should post the stored procedure you wrote.

Sadique KT 24-Nov-15 4:52am

question improved...

jgakenhe 23-Nov-15 21:25pm

You'll need to write subqueries in your select statement to access the child fields.

Sadique KT 24-Nov-15 0:31am

can you please write the query to get the above result. A.Col1 & B.Col2 is PK and B.Col1 is FK.

jaket-cp 24-Nov-15 4:00am

As F-ES Sitecore has suggested, supply your stored proc so we may be able to help more.

But if you have not check it out already, maybe tsql aggregate functions with over may help.
Have a read of:
https://msdn.microsoft.com/en-GB/library/ms173454.aspx
https://msdn.microsoft.com/en-GB/library/ms189461.aspx

jgakenhe 24-Nov-15 5:42am

You have to try. I'm not going to do all of your homework for you.

Sadique KT 24-Nov-15 5:52am

I just updated... and you are not supposed to do my home work..

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-11-25T03:33:00

If you join the tables on Col1 e.g.

SQL

select A.Col1 AS Col1, A.Col2 as Col2, [status]
From TableA A
left outer join TableB B on A.Col1 = B.Col1

you get the following ...

1	A1	1
1	A1	1
2	A2	0
2	A2	1
3	A3	1
4	A4	NULL

(I added an extra row to the parent table for which there were no child table entries just to test my query). I.e. you get 0,1, or 2 rows in the results per row on the parent table.

You can use that output with PIVOT to get the data as you have presented it

SQL

select Col1, Col2, [1] as ActiveCount,[0] as InactiveCount
FROM
(
	select A.Col1 AS Col1, A.Col2 as Col2, [status]
	From TableA A
	left outer join TableB B on A.Col1 = B.Col1
) AS src
PIVOT
(
	count([status]) for [status] in ([0],[1])
) as pvt

It's reasonably efficient and can be made more so if there are appropriate primary and foreign keys defined

For further reference see
Visual Representation of SQL Joins[^]
SQL Wizardry Part Seven - PIVOT and arbitrary lists of data[^]
Database performance optimization part 1 (Indexing strategies)[^]

How to get child table row count with master table columns in single query

1 solution

Solution 2

Add your solution here

Preview 0

How to get child table row count with master table columns in single query

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us