Results 1 to 4 of 4

Thread: [RESOLVED] SQL View. Join tables and get a counts WHERE some value is null or not

  1. #1

    Thread Starter
    Hyperactive Member Krokonoster's Avatar
    Join Date
    Jan 2010
    Location
    Cape Town
    Posts
    448

    Resolved [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


  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member Krokonoster's Avatar
    Join Date
    Jan 2010
    Location
    Cape Town
    Posts
    448

    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.


  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width