Left Join variable not valid

[Sfj]

Hello folks,

Old programmer learning new tricks.

I think similar issues have been posted, but I would like to ask some help
with the following code...

select [t1.*],[t2.userid] as [userid2],[t2.approved],[t2.type]
from [tblMembers] as t1 left outer join [tblProfiles] as t2
on [t1.userid]=[t2.userid]
where ...

I get the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
't1.userid'.

....but there is a [userid] in both tables. Do I have to make them keys or
something? And I need to do work (add and update) on both tables after the
values are returned.

Also, how bad is my syntax? If you could give advice on removing extraneous
brackets and I would like to not have to use the "as" clause. I really just
want to keep it simple. Whatever works though.

Thanks

[Dan Guzman]

> select [t1.*],[t2.userid] as [userid2],[t2.approved],[t2.type]

from [tblMembers] as t1 left outer join [tblProfiles] as t2
on [t1.userid]=[t2.userid]
The main problem here is that you are enclosing both the table alias and
column name. Try:

SELECT
[t1].*,
[t2].[userid] AS [userid2],
[t2].[approved],
[t2].[type]
FROM [tblMembers] AS [t1]
LEFT OUTER JOIN [tblProfiles] AS [t2]
ON [t1].[userid] = [t2].[userid]
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sfj" <sh*******@yahoo.com> wrote in message
news:6h2lg.112415$Ce1.107196@dukeread01... Hello folks,

Old programmer learning new tricks.

I think similar issues have been posted, but I would like to ask some help
with the following code...

select [t1.*],[t2.userid] as [userid2],[t2.approved],[t2.type]
from [tblMembers] as t1 left outer join [tblProfiles] as t2
on [t1.userid]=[t2.userid]
where ...

I get the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
't1.userid'.

...but there is a [userid] in both tables. Do I have to make them keys or
something? And I need to do work (add and update) on both tables after
the values are returned.

Also, how bad is my syntax? If you could give advice on removing
extraneous brackets and I would like to not have to use the "as" clause.
I really just want to keep it simple. Whatever works though.

Thanks

[Hugo Kornelis]

On Sat, 17 Jun 2006 20:47:20 -0500, Sfj wrote:

(snip)

Also, how bad is my syntax? If you could give advice on removing extraneous
brackets
Hi Sfj,

Removing brackets is a great idea - IMO, they only make the query
unreadable. You only really need to enclose a table name, column name,
or other identifier with either brackets (the MS standard that works
across SQL Server and Access, but not outisde the MS world) or double
quotes (the ANSI SQL standard) if it doesn't follow the rules for
identifiers as laid down in Books Online. Most common reasons for
needing brackets or double quotes are: identifiers with embedded spaces,
using reserved words as identifier, using special characters in
identifier or starting identifier with numeric.

If you stick to the rules for identifier names, you'll never need to
delimit them at all.
and I would like to not have to use the "as" clause. I really just
want to keep it simple. Whatever works though.

Though the AS is optional, I recommend always sticking it in. It's just
three extra keystrokes, and it makes your query lots easier to read.

--
Hugo Kornelis, SQL Server MVP

[shermanfj]

Thank you, gentlemen.

Your answers helped me to resolve and move on.