Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:

SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

Join Issue

I am getting this error message:

The column prefix 'TrainingData' does not match with a table name or alias name used in the query.

From this code (asp/ado/sql server):

sql = "SELECT FirstName, LastName, Grade, DayDate, Effort, UnitMeas,
Partners, Comments"
sql = sql & "FROM PartData, TrainingData WHERE TrainingData.PartID =
PartData.PartID AND TeamID = " & Session("teamid") & ""

Set rs=conn.Execute(sql)

Anyone see anything wrong with it? The tables are named correctly.

Thanks!no space between Comments and FROM

rudy
http://r937.com/|||...the sound you hear is me hitting my head on the table.

Thanks!@.!!!