Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Efficient Singular / Plural child records syntax

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2014-11-14 : 08:53:31
I want to create a view:

CREATE MyView
AS
SELECT Col1,
Col2,
[ChildRecordCount] =
CASE WHEN MyChildRowCount = 0
THEN NULL
ELSE CONVERT(varchar(20), MyChildRowCount)
+ ' Child record'
+ CASE WHEN MyChildRowCount = 1 THEN '' ELSE 's'
END
FROM MyTable AS T
JOIN (
SELECT SomePkID,
[MyChildRowCount] = COUNT(*)
FROM MyChildTable AS C
GROUP BY SomePkID
) AS C
ON C.SomePkID = T.SomePkID

I don't like the fact that I cannot reference T.SomePkID inside the inner nested Query (out of scope) as it feels like the nested query may be inefficient.

I can't (I presume?) use the Nested Query in the outser SELECT's assignment to [ChildRecordCount] as I would need it multiple times.

I was wondering if I could use a

[MyChildRowCount] = COUNT(*) OVER(PARTITION BY C.SomePkID)

somehow that would be more efficient ...

... or something else perhaps?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-14 : 08:59:19
If you use CROSS APPLY instead of JOIN , then you can reference T.SomePkID inside the inner nested Query, e.g.


...
CROSS APPLY (SELECT SomePkID,
[MyChildRowCount] = COUNT(*)
FROM MyChildTable AS C
GROUP BY SomePkID
HAVING SomePkID = T.SomePkID
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2014-11-14 : 09:13:01
Doh! That's great, thanks, I'll give it a bash.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2014-11-16 : 03:33:53
Worked a treat, thanks

I'm clearly getting old and stuck in the mindset that CROSS APPLY was intended to be used for Functions ... hopefully I will be put out to grass soon!
Go to Top of Page
   

- Advertisement -