INNER JOIN CHALLENGE

Question

INNER JOIN CHALLENGE

rpalacios 19022

SSC Journeyman

Points: 95
More actions
September 15, 2010 at 11:38 am

#387794

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

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/

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

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

rpalacios 19022 SSC Journeyman Points: 95 More actions · Answer 3

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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

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...

- Craig Farrell

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

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.

rpalacios 19022 SSC Journeyman Points: 95 More actions · Answer 6

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.

Cory E. SSCertifiable Points: 7076 More actions · Answer 7

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 8

Ah crap, I did. Didn't even notice that. Hard to follow the logic of this query..working on it.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

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:

- Craig Farrell

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 10

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.

rpalacios 19022 SSC Journeyman Points: 95 More actions · Answer 11

Derrick,

Attached is a doc illustrating all the tables and columns.

Thanks for all the help!

rpalacios 19022 SSC Journeyman Points: 95 More actions · Answer 12

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

rpalacios 19022 SSC Journeyman Points: 95 More actions · Answer 13

Thanks again guys and Derrick. I'll pitch this report to the user and see how they like it.

-rp

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

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?