SQL: internal join in alias column

Question

Earlier, I asked to remove the text from the field and convert it to int, this works successfully. But now I would like to make an INNER JOIN for this new value.

So, I have this:

SELECT CONVERT(int, SUBSTRING(accountingTab.id, PATINDEX('%[0-9]%', accountingTab.id), 999)) 
AS 'memId',  userDetails.title, userDetails.lname 
FROM accountingTab INNER JOIN
(SELECT id, title, first, last FROM memDetTab) AS userDetails ON memID = userDetails.id

And then I get an Invalid Column Name error memID.

How can i fix this?

Answer 1

If you need to do this, you have design issues. If you are able, I would suggest you reorganize your table or relationship.

Answer 2

You can either repeat the entire expression or change your connection:

SELECT *
FROM memDetTab
    JOIN (SELECT CONVERT(int, SUBSTRING(accountingTab.id, PATINDEX('%[0-9]%', accountingTab.id), 999)) AS 'memId', userDetails.title, userDetails.lname
FROM accountingTab) subquery
    ON subquery.memID = memDetTab.ID

Answer 3

Instead of memId, repeat the entire expression.