Invalid Column name when using INNER JOIN to get values from two tables

Question

I have two tables:

tbl_profiles_and_users:

[id] [int] IDENTITY(1,1) NOT NULL,
[profile_id] [int] NOT NULL,
[user_id] [nvarchar](50) NULL

tbl_profiles_and_activities:

[id] [int] IDENTITY(1,1) NOT NULL,
[profile_id] [int] NOT NULL,
[user_id] [nvarchar](50) NOT NULL,
[activity_name] [nvarchar](50) NULL,
[check] [int] NULL,
[preferred] [int] NULL

both tables have entries in them, and I am trying to select the user, and activities, only from within a specified profile.

I am trying to do this with the following join:

use [dbName]

SELECT p1.user_id AS User_ID, [p2.activity_name] as [Activity_Name]
FROM tbl_profiles_and_users AS p1 
INNER JOIN tbl_profiles_and_activities AS p2 
ON p1.user_id = p2.user_id AND p1.profile_id = p2.profile_id

but I receive the message Invalid column name 'p2.activity_name' What am I doing wrong? Can I achieve the desired results with a Join?

John Woo · Accepted Answer · 2012-11-08 15:18:27Z

6

because you are escaping the whole column, thus, the server reads it as [p2.activity_name] as whole column name which is very different from [p2].[activity_name]

SELECT p1.user_id AS User_ID, [p2].[activity_name] as [Activity_Name]
FROM ....

answered Nov 8, 2012 at 15:18

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

D'Arcy Rittich Over a year ago

(Actually a delimited identifier, not escaped.)

Christian Over a year ago

Thank you, this was the solution. I will accept your answer when the timer allows it.

John Woo Over a year ago

@RedFilter yeah you're right. was trying to figure out what the correct term. Thanks!

Collectives™ on Stack Overflow

Invalid Column name when using INNER JOIN to get values from two tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related