Rewrite query using LEFT JOIN

Question

Post reply

Rewrite query using LEFT JOIN

T.Ashish

SSCarpal Tunnel

Points: 4248
More actions
April 18, 2013 at 1:33 am

#394618

Can this query be written using LEFT JOIN to "person" as part of the FROM clause, rather than doing subquery?
Query, IO statics and execution plan is attached. Unfortunately test data is not available.
Regards.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

This should do the work for you

SELECTCASE WHEN C.active_flag = 1 THEN 'Active' ELSE 'Inactive' END AS active_flag,

C.name, C.company_code,

SUM( CASE WHEN P.active_flag = 1 AND P.unregister_flag = 0 THEN 1 ELSE 0 END ) AS active_users,

SUM( CASE WHEN P.active_flag = 1 AND P.unregister_flag = 1 THEN 1 ELSE 0 END ) AS new_users,

SUM( CASE WHEN P.active_flag = 0 THEN 1 ELSE 0 END ) AS inactive_users,

C.test_company_flag, C.parent_company_flag

FROMdbo.company AS C

LEFT OUTER JOINdbo.person AS P ON C.company_code = P.company_code

GROUP BY CASE WHEN C.active_flag = 1 THEN 'Active' ELSE 'Inactive' END,

C.name, C.company_code, C.test_company_flag, C.parent_company_flag

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Or this:

SELECT

active_flag = CASE WHEN c.active_flag = 1 THEN 'Active' ELSE 'Inactive' END,

c.name,

c.company_code,

d.active_users,

d.new_users,

d.inactive_users,

test_company_flag,

parent_company_flag

FROM dbo.company c

LEFT JOIN (

SELECT

company_code,

active_users= SUM(CASE WHEN active_flag = 1 AND unregister_flag = 0 THEN 1 END),

new_users= SUM(CASE WHEN active_flag = 1 AND unregister_flag = 1 THEN 1 END),

inactive_users= SUM(CASE WHEN active_flag = 0 THEN 1 END)

FROM dbo.person

GROUP BY company_code

) d ON d.company_code = c.company_code

What do you mean by "Unfortunately test data is not available"? Why can't you make some? You could obfuscate the company name and Bob's your uncle.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 3

Dhasian, Chris

Thanks for your kind support.

Dhasian,

The query is working fine and much better then before. Good Job. But adding a where clause results in increased scan counts on "person".

Chris,

The query works equally good even with where clause.

OLD

Table 'person'. Scan count 3, logical reads 9593

NEW

Table 'person'. Scan count 1, logical reads 4498