SQL: inner join on alias column

Question

Hi all,

Previously I have asked to strip text from a field and convert it to an int, this works successfully. But now I would like to do an INNER JOIN on 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 the Invalid Column Name 'memID' error.

Anybody a suggestion on how I can fix this?

Thanks in advance!

P.s. this naming convention is just as an example to simplify the scenario, in case you were wondering ;-)

Answer 1

Instead of memId, repeat the whole expression.

Answer 2

If you have to do this, you have design problems. If you're able, I would suggest you need to refactor your table or relationships.

Answer 3

You can either repeat the whole expression or reverse your join:

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