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

RE: [SQLQueriesNoCode] SQL Question

Expand Messages
  • 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 1 of 2 , May 9, 2012
    • 0 Attachment
      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.