RE: [SQLQueriesNoCode] SQL Question
- 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.
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
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Andy Mills
Sent: Wednesday, May 09, 2012 12:03 PM
Subject: [SQLQueriesNoCode] SQL Question
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:
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
ISNULL(p.FirstName,'') as 'FirstName',
ISNULL(p.LastName,'') as 'LastName',
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
inner join aspnet_membership m on
inner join aspnet_usersinroles ur on
inner join aspnet_roles r on r.roleid=ur.roleid
WHERE (MembershipLevel = 'Premium')
[Non-text portions of this message have been removed]
Yahoo! Groups Links