How to use a calculated field in a join?

[heavyEddie]

I've searched around, but this type of question is really hard to find any kind of hits with.

 

So... I have two tables. 

• The first has a field called "id" that can be numeric or have "c" prefixed to a number.
  
• The second has a field called "cid" that will match the "id" field with the prefixed "c"
  

 

So, the challenge is to join the tables and strip the prefixed "c" on the "id" field and then use it in the join.

 

My query looks like this, but it doesn't work since it will not recognize cid.

SELECT s.id, s.display, IF(LEFT(s.id, 1) = 'c', SUBSTRING(s.id,2), s.id) as cid, c.name
FROM slibrary AS s INNER JOIN clibrary AS c ON cid = c

 

Is this possible?

[teng84]

yep but can we have sample output or expected value to join etcc....

[heavyEddie]

Field and Values in slibrary

id = 1          display = 'yes'

id = 2          display = 'yes'

id = c999    display = 'no'

id = 5          display = 'yes'

id = c822    display = 'yes'

 

Field and Values in clibrary

cid = 999      name = 'how to walk a dog'

cid = 822      name = 'how to wlak a cat'

 

Desired output

id          display          name

999      no                  how to walk a dog

822      yes                how to walk a cat

 

[fenway]

My query looks like this, but it doesn't work since it will not recognize cid.

SELECT s.id, s.display, IF(LEFT(s.id, 1) = 'c', SUBSTRING(s.id,2), s.id) as cid, c.name
FROM slibrary AS s INNER JOIN clibrary AS c ON cid = c

 

That's because the aliasing is done after the result set is calculated, which is too late for the join.

 

It would be expensive, but if you created a derived table with the correct column name, then you could join in this table, and it would be a "proper" column.