-
Aug 14th, 2013, 12:27 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] SQL View. Join tables and get a counts WHERE some value is null or not
Hi,
I got a Clients (C) table and a ClientMembers (CM) table and want to create a view with columns: C.Id | C.Name | CM.MemberCount | CM.DependantsCount
What distinguish ClientMember from being a Member or Dependant is the presence or absence of a "ClientMemberFk" value (If none, you are a member. If set, you are a dependent of the member by that Fk)
The following does not work (my sql rusted beyond belief after years of using ORMs)
It say "Invalid object name 'CM'." Where I try do the count.
Any advice would be really helpful.
Code:
ALTER VIEW ClientListItems
AS
SELECT
C.Id,
C.Code,
C.Name,
C.RegistrationDate,
(SELECT COUNT(*) FROM CM WHERE CM.ClientMemberFk IS NULL) AS MemberCount,
(SELECT COUNT(*) FROM CM WHERE CM.ClientMemberFk IS NOT NULL) AS DependantCount
FROM Clients C
LEFT JOIN ClientMembers CM ON C.Id = CM.ClientFk
-
Aug 14th, 2013, 02:10 PM
#2
Re: SQL View. Join tables and get a counts WHERE some value is null or not
SELECT COUNT(*) FROM CM WHERE CM.ClientMemberFk IS NULL
There is no CM... CM is an alias, but alas you cannot select from it... try this instead.. a cross apply...
Code:
SELECT
C.Id,
C.Code,
C.Name,
C.RegistrationDate,
MC.TotalCount AS MemberCount,
DC.TotalCount AS DependantCount
FROM Clients C
cross apply (SELECT COUNT(*) as TotalCount FROM ClientMembers CM where C.Id = CM.ClientFk and CM.ClientMemberFk IS NULL) as MC
cross apply (SELECT COUNT(*) as TotalCount FROM ClientMembers CM where C.Id = CM.ClientFk and CM.ClientMemberFk IS NOT NULL) as DC
-tg
-
Aug 14th, 2013, 03:41 PM
#3
Thread Starter
Hyperactive Member
Re: SQL View. Join tables and get a counts WHERE some value is null or not
awesome dude, thanks a lot.
recommend a good "quick reference" book or site? Going to be writing a whole lot of sql again after all the years, and all will involve joins, grouping, etc.
-
Aug 14th, 2013, 03:43 PM
#4
Re: SQL View. Join tables and get a counts WHERE some value is null or not
msdn, google... I use the "T-sql" followed by what I'm searching for... so "t-sql cross join" ... cross apply, left join, full outer join... etc...
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|