Loading ...
Sorry, an error occurred while loading the content.
 

SQL Question

Expand Messages
  • Andy Mills
    Hello Everyone: I am wondering if someone could help me with a SQL issue. I have table called Profile . This is a self-relating table. It has the following
    Message 1 of 2 , May 9, 2012
      Hello Everyone:

      I am wondering if someone could help me with a SQL issue.

      I have table called "Profile".

      This is a self-relating table.

      It has the following columns:
      UserID,
      AccountManagerUserName,
      RoleType

      For every record, there could self relating records based on AccountManagerUserName. If two records have the same AccountManagerUserName, they are "sub-accounts" of the main account.

      I need to find a count for the number of "Sub-Accounts" for each user (userID) where the RoleType = 'Educator'

      I know the table has to do an inner join on itself but for some reason, I just can't get this query working.

      I should note that ultimately, this query is going to be plugged into a larger query. The entire query is below (you can see, the Profile table is already referenced). The parts I am going to add to it are 'Teacher Accounts' and 'Sub Accounts'.

      I thought the best way to handle getting those is a sub-query inside the main query. The main query works and was developed a few years ago. I am simply trying to extend it to get two more pieces of information ("Teacher Accounts" and "Sub Accounts") without causing harm to the original data.

      SELECT
      r.RoleName,
      ISNULL(p.FirstName,'') as 'FirstName',
      ISNULL(p.LastName,'') as 'LastName',
      m.Email,
      am.UserName,
      am.ExpirationDate,
      am.OrderId,
      am.SignupDate,
      m.password,
      ISNULL(hh.Name,'') as 'HowHeard',
      m.CreateDate as 'Account Created',
      '1' AS 'Teacher Accounts',
      '2' AS 'Sub Accounts'
      from dbo.AllMembers am
      left outer join profile p on am.userid=p.userid
      left outer join MarketingCampaign hh on hh.MarketingCampaignId=p.CampaignId
      inner join aspnet_membership m on am.userid=m.userid
      inner join aspnet_usersinroles ur on am.userid=ur.userid
      inner join aspnet_roles r on r.roleid=ur.roleid
      WHERE (MembershipLevel = 'Premium')

      Thanks
      Andy



      [Non-text portions of this message have been removed]
    • Arnie Rowland
      If you can locate on the interweb a copy of the Northwind database for SQL Server, you will see how the Employees table self-references. Normally, you would
      Message 2 of 2 , May 9, 2012
        If you can locate on the interweb a copy of the Northwind database for
        SQL Server, you will see how the Employees table self-references.

        Normally, you would have a key column, such as the UserID, and another,
        perhaps names AccountManagerUserId. Both would contain user IDs -but the
        AccountManagerId would reference to a UserID.

        You can't self-reference with the three columns you have indicated.


        Regards,

        Arnie Rowland | Westwood Consulting, LLC | SQL Server MVP

        "You cannot do a kindness too soon because you never know how soon
        it will be too late."
        -Ralph Waldo Emerson




        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Andy Mills
        Sent: Wednesday, May 09, 2012 12:03 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] SQL Question

        Hello Everyone:

        I am wondering if someone could help me with a SQL issue.

        I have table called "Profile".

        This is a self-relating table.

        It has the following columns:
        UserID,
        AccountManagerUserName,
        RoleType

        For every record, there could self relating records based on
        AccountManagerUserName. If two records have the same
        AccountManagerUserName, they are "sub-accounts" of the main account.

        I need to find a count for the number of "Sub-Accounts" for each user
        (userID) where the RoleType = 'Educator'

        I know the table has to do an inner join on itself but for some reason,
        I just can't get this query working.

        I should note that ultimately, this query is going to be plugged into a
        larger query. The entire query is below (you can see, the Profile table
        is already referenced). The parts I am going to add to it are 'Teacher
        Accounts' and 'Sub Accounts'.

        I thought the best way to handle getting those is a sub-query inside the
        main query. The main query works and was developed a few years ago. I
        am simply trying to extend it to get two more pieces of information
        ("Teacher Accounts" and "Sub Accounts") without causing harm to the
        original data.

        SELECT
        r.RoleName,
        ISNULL(p.FirstName,'') as 'FirstName',
        ISNULL(p.LastName,'') as 'LastName',
        m.Email,
        am.UserName,
        am.ExpirationDate,
        am.OrderId,
        am.SignupDate,
        m.password,
        ISNULL(hh.Name,'') as 'HowHeard',
        m.CreateDate as 'Account Created',
        '1' AS 'Teacher Accounts',
        '2' AS 'Sub Accounts'
        from dbo.AllMembers am
        left outer join profile p on am.userid=p.userid
        left outer join MarketingCampaign hh on
        hh.MarketingCampaignId=p.CampaignId
        inner join aspnet_membership m on
        am.userid=m.userid
        inner join aspnet_usersinroles ur on
        am.userid=ur.userid
        inner join aspnet_roles r on r.roleid=ur.roleid
        WHERE (MembershipLevel = 'Premium')

        Thanks
        Andy



        [Non-text portions of this message have been removed]



        ------------------------------------

        Yahoo! Groups Links
      Your message has been successfully submitted and would be delivered to recipients shortly.