September 15, 2010 at 11:38 am
Hello guys,
I'm using a SELECT query to join data from two different databases that reside in the same SQL server. The challenge I have is that the AfsMembership.dbo.aspnet_Users and the AfsMembership.dbo.aspnet_Roles tables do not have any columns or PK's that I can use to link them to my other tables (below).
Can you use an alias or something to join them?
Here are the 2 databases and all the tables that I am referencing in my query;
AfsDepositGateway.dbo.MerchantAccounts.Account, AfsDepositGateway.dbo.Merchants.DisplayName, AfsDepositGateway.dbo.MerchantUserAccounts.UserName,
CustomerContacts.EmailTo, AfsMembership.dbo.aspnet_Users, AfsMembership.dbo.aspnet_Roles
Here's the query I'm trying to use:
SELECT distinct MerchantAccounts.Account, Merchants.DisplayName, MerchantUserAccounts.UserName,
CustomerContacts.EmailTo, aspnet_Roles.RoleName
FROM AfsDepositGateway.dbo.MerchantAccounts AS MerchantAccounts
INNER JOIN Merchants ON MerchantAccounts.MerchantId = Merchants.MerchantId
INNER JOIN MerchantUserAccounts ON Merchants.MerchantId = MerchantUserAccounts.MerchantId
INNER JOIN CustomerContacts ON Merchants.CustomerContactId = CustomerContacts.CustomerContactId
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users1 ON MerchantUserAccounts.Username = aspnet_Users1.UserName
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users2 ON aspnet_Roles.ApplicationId = aspnet_Users2.ApplicationId
INNER JOIN AfsMembership.dbo.aspnet_Roles aspnet_Roles1 ON aspnet_users.ApplicationId = aspnet_Roles1.ApplicationId
Here's the error I'm getting:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_Roles.ApplicationId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_users.ApplicationId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_Roles.RoleName" could not be bound.
All help is greatly appreciated.
-rp
September 15, 2010 at 12:08 pm
You can alias the tables for ease of use, but you need to use the correct aliases. For clarification, you should use the 3 part naming convention on all tables (when crossing databases like this), just so someone down the road will know where they are coming from.
SELECT distinct MerchantAccounts.Account, Merchants.DisplayName, MerchantUserAccounts.UserName,
CustomerContacts.EmailTo, aspnet_Roles1.RoleName
FROM AfsDepositGateway.dbo.MerchantAccounts AS MerchantAccounts
INNER JOIN Merchants ON MerchantAccounts.MerchantId = Merchants.MerchantId
INNER JOIN MerchantUserAccounts ON Merchants.MerchantId = MerchantUserAccounts.MerchantId
INNER JOIN CustomerContacts ON Merchants.CustomerContactId = CustomerContacts.CustomerContactId
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users1 ON MerchantUserAccounts.Username = aspnet_Users1.UserName
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users2 ON aspnet_Roles1.ApplicationId = aspnet_Users2.ApplicationId
INNER JOIN AfsMembership.dbo.aspnet_Roles aspnet_Roles1 ON aspnet_users1.ApplicationId = aspnet_Roles1.ApplicationId
Here's the error I'm getting:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_Roles.ApplicationId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_users.ApplicationId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_Roles.RoleName" could not be bound.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 15, 2010 at 12:21 pm
As Mike said, if you create an alias for a table, you need to use that alias for every mention of the table. You can't mix and match. Also, I cleaned up the code a bit (but can't verify due to not having the schema here)..you might be able to read this better
SELECT distinct MA.Account
, M.DisplayName
, MUA.UserName
, CC.EmailTo
, aspnet_Roles1.RoleName
FROM AfsDepositGateway.dbo.MerchantAccounts MA
INNER JOIN Merchants M ON
M.MerchantId = MA.MerchantId
INNER JOIN MerchantUserAccounts MUA ON
MUA.MerchantId = M.MerchantID
INNER JOIN CustomerContacts CC ON
CC.CustomerContactId = M.CustomerContactId
INNER JOIN AfsMembership.dbo.aspnet_Roles aspnet_Roles1 ON
aspnet_users1.ApplicationId = aspnet_Roles1.ApplicationId
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users1 ON
aspnet_Users1.UserName = MUA.Username AND
aspnet_Users1.ApplicationID = aspnet_Roles1.ApplicationID
September 15, 2010 at 12:32 pm
Thanks for replies Mike and Derrick.
Derrick, when I execute the new query, I'm getting the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_users1.ApplicationId" could not be bound.
The query does look a lot more clean.
September 15, 2010 at 12:46 pm
rpalacios 19022 (9/15/2010)
Thanks for replies Mike and Derrick.Derrick, when I execute the new query, I'm getting the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_users1.ApplicationId" could not be bound.
The query does look a lot more clean.
Curious, does your db run with case sensitivity? The reason I ask, and it may just be a typo, is that your error copy is aspnet_users1, and the alias is aspnet_Users1. However, in the ON clause for Group1, it uses a lowercase users1. Try correcting that to Users1...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 12:51 pm
Unfortunately I'm writing this blind with no real way to quickly check syntax..if you could post table definitions, it would help a bunch.
September 15, 2010 at 12:51 pm
Hello Craig,
I don't think so. I tried using uppercase and still got the same error.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "aspnet_Users1.ApplicationId" could not be bound.
September 15, 2010 at 12:55 pm
INNER JOIN AfsMembership.dbo.aspnet_Roles aspnet_Roles1
ON aspnet_users1.ApplicationId = aspnet_Roles1.ApplicationId
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users1
ON aspnet_Users1.UserName = MUA.Username
AND aspnet_Users1.ApplicationID = aspnet_Roles1.ApplicationID
You are referencing aspnet_users1 before you joined to it.
-- Cory
September 15, 2010 at 12:59 pm
Ah crap, I did. Didn't even notice that. Hard to follow the logic of this query..working on it.
September 15, 2010 at 1:03 pm
Derrick Smith (9/15/2010)
Ah crap, I did. Didn't even notice that. Hard to follow the logic of this query..working on it.
If it makes you feel better, I just did a facepalm when I realized I missed it too and went off on possible collation issues... for an alias. :blush:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 15, 2010 at 1:05 pm
SELECT distinct MA.Account
, M.DisplayName
, MUA.UserName
, CC.EmailTo
, aspnet_Roles1.RoleName
FROM AfsDepositGateway.dbo.MerchantAccounts MA
INNER JOIN Merchants M ON
M.MerchantId = MA.MerchantId
INNER JOIN MerchantUserAccounts MUA ON
MUA.MerchantId = M.MerchantID
INNER JOIN CustomerContacts CC ON
CC.CustomerContactId = M.CustomerContactId
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users1 ON
aspnet_Users1.UserName = MUA.Username
INNER JOIN AfsMembership.dbo.aspnet_Roles aspnet_Roles1 ON
aspnet_Roles1.ApplicationID = aspnet_Users1.ApplicationID
This should work..there was basically a redundant inner join on the first try too. Give that a shot and see if you get results.
September 15, 2010 at 1:11 pm
Derrick,
Attached is a doc illustrating all the tables and columns.
Thanks for all the help!
September 16, 2010 at 10:24 am
Derrick,
The query worked exactly as you predicted. Thanks a whole lot!!
The only thing I need to workout now is the sorting for the columns. If I wanted the username, name and e-mail columns to match, what would be the best way to order by?
-rp
September 16, 2010 at 11:57 am
Thanks again guys and Derrick. I'll pitch this report to the user and see how they like it.
-rp
September 16, 2010 at 7:12 pm
You're also not using ASPNET_USER2 at all (at least in the part you're showing us). I can't help but think that's not a table you want in your query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply