INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INNER JOIN

Author

Topic

rpalacios-sql
Starting Member

6 Posts

Posted - 2010-09-14 : 11:27:32

Hello guys,

I need to better understand the conditions that have to be met in order to succesfully execute a SELECT query to join different tables in different dadatabes, all in the same SQL server.

I've located the databases and tables that I would like to join but there is no direct relationship between the tables. No primary key or a column that has the same data.

Thanks,

-rp

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-14 : 11:59:50

So, is there an intermediary table that translates the fields you want to join?

Without this, you would have trouble matching records in one table with records in another, so guess there must be a table somewhere..

rpalacios-sql
Starting Member

6 Posts

Posted - 2010-09-14 : 12:23:22

Hello Rick,

Thanks for the reply. In fact, there is an intermediary table I am trying to use. Below is the query I'm using along with the databases, tables and columns I am referencing. Both the AfsDepositGateway.dbo.MerchantUserAccounts.UserName and the AfsMembership.dbo.aspnet_Users.UserName have the username column, but when I execute the query i get a correlation error (below).

Query:
SELECT distinct AfsDepositGateway.dbo.MerchantAccounts.Account, AfsDepositGateway.dbo.Merchants.DisplayName, AfsDepositGateway.dbo.MerchantUserAccounts.UserName,
AfsDepositGateway.dbo.CustomerContacts.EmailTo, AfsMembership.dbo.aspnet_Roles.RoleName, AfsMembership.dbo.aspnet_Users
FROM AfsDepositGateway.dbo.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 ON AfsDepositGateway.dbo.MerchantUserAccounts.Username = AfsMembership.dbo.aspnet_Users.UserName
INNER JOIN AfsMembership.dbo.aspnet_Users ON AfsMembership.dbo.aspnet_Roles.ApplicationId = AfsMembership.dbo.aspnet_Users.ApplicationId

Error:
Msg 1013, Level 16, State 1, Line 1
The objects "AfsMembership.dbo.aspnet_Users" and "AfsMembership.dbo.aspnet_Users" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-14 : 12:32:53

A few things,

1. You can not use the same name twice in your joins, if you want to use the same table twice, Alias it:


INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users1 ON AfsDepositGateway.dbo.MerchantUserAccounts.Username = aspnet_Users1.UserName
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users2 ON AfsMembership.dbo.aspnet_Roles.ApplicationId = aspnet_Users2.ApplicationId

2. You are not joining aspnet_Roles, so you can not use it in the query, if you need to use it, you can join to AfsMembership.dbo.aspnet_Users and then use this to join to aspnet_Roles if the records are the ones you want.

3. Aliasing the joimns can make the query much more readable, rather than putting in the dbname, owner and table everytime. i.e.

AfsDepositGateway.dbo.MerchantAccounts.Account

Could be

MerchantAccounts.Account

in the select list..

rpalacios-sql
Starting Member

6 Posts

Posted - 2010-09-14 : 13:00:24

Hello Rick,

I have modified my select query using the methods you suggested. However, now I'm getting the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "AfsMembership.dbo.aspnet_Roles.ApplicationId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "AfsMembership.dbo.aspnet_Roles.RoleName" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "AfsMembership.dbo.aspnet_Users" could not be bound.

Here's the new query:

SELECT distinct AfsDepositGateway.dbo.MerchantAccounts.Account, AfsDepositGateway.dbo.Merchants.DisplayName, AfsDepositGateway.dbo.MerchantUserAccounts.UserName,
AfsDepositGateway.dbo.CustomerContacts.EmailTo, AfsMembership.dbo.aspnet_Roles.RoleName, AfsMembership.dbo.aspnet_Users
FROM AfsDepositGateway.dbo.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 AfsDepositGateway.dbo.MerchantUserAccounts.Username = aspnet_Users1.UserName
INNER JOIN AfsMembership.dbo.aspnet_Users aspnet_Users2 ON AfsMembership.dbo.aspnet_Roles.ApplicationId = aspnet_Users2.ApplicationId

BTW - Thanks for alias tip, I didn't know about that one.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-14 : 14:13:11

you need to use the alias in your select

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rpalacios-sql
Starting Member

6 Posts

Posted - 2010-09-14 : 15:03:37

Hello X00,

Can you give an example. I'm not too familiar with this approach.

Thanks,

-rp

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-15 : 04:20:16

[code]
SELECT distinct MerchantAccounts.Account, Merchants.DisplayName, MerchantUserAccounts.UserName,
CustomerContacts.EmailTo, aspnet_Roles.RoleName, aspnet_Users.<whichfielddoyouwanthere?>
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
[/code]
You still do not have aspnet_Roles in your join anywhere.

rpalacios-sql
Starting Member

6 Posts

Posted - 2010-09-15 : 10:41:12

Hello Rick,

I don't think we need the aspnet_Users.<whichfielddoyouwanthere?> column since I'm getting this information from the MerchantUserAccounts.UserName column.

New Query:
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

Error:

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.

If we expand on the aliasing concept. Is it used to join tables that don't have the same column ID or data?

Thanks,

-rp

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-15 : 11:04:42

See my first post, you do not have aspnet_Roles in your select anywhere, so how do you expect the rest of the query to know what this table is?

rpalacios-sql
Starting Member

6 Posts

Posted - 2010-09-15 : 12:18:38

Hello Rick,

Is this not it?

SELECT distinct MerchantAccounts.Account, Merchants.DisplayName, MerchantUserAccounts.UserName,
CustomerContacts.EmailTo, aspnet_Roles.RoleName

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-09-16 : 04:40:54

No, it needs to be somewhere after FROM like the rest of the joins.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources