Efficient Singular / Plural child records syntax

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

Kristen
Test

22859 Posts

Posted - 2014-11-14 : 09:13:01

Doh! That's great, thanks, I'll give it a bash.

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources