Convoluted Join - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Convoluted Join

Author

Topic

sillybee
Starting Member

2 Posts

Posted - 2009-09-28 : 11:48:37

This is a question posed to me by someone else and I'll be d*mned if I know. (Pull a couple answers out of your arse a few times and suddenly you're the "expert")

PERSON Table
------
SYSTEM_ID
FULL_NAME

PROFILE TABLE
-----
SYSTEM_ID
TYPIST
APPLICATION
OBJ_TYPE

> Query should return PERSON.FULL_NAME and a count of matching entries in PROFILE table, even if the count is zero, where the following criteria are met:
> PERSON.SYSTEM_ID = PROFILE.TYPIST
> OBJ_TYPE = 0 or OBJ_TYPE is null, APPLICATION <> 2

Any help greatly appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-28 : 13:13:33

Maybe this??

SELECT a.FULL_NAME, 
       COALESCE(b.CNT,0) 
FROM   PERSON a 
       LEFT JOIN (SELECT   SYSTEM_ID, 
                            COUNT(* ) AS CNT 
                   FROM     PROFILE 
                   WHERE    SYSTEM_ID = TYPIST 
                            AND COALESCE(OBJ_TYPE,0) = 0 
                            AND APPLICATION <> 2 
                   GROUP BY SYSTEM_ID) b 
         ON a.SYSTEM_ID = b.SYSTEM_ID

sillybee
Starting Member

2 Posts

Posted - 2009-09-28 : 18:49:01

Many thanks,

I was thinking something along the same lines once I had chance to think about it, but the coalesce bit never occurred to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources