SQL TABLE JOIN

[wonder FOOL]

Hello all, I dont know it might be an easy thing but i didnt work it around. I have two tables and want to join them. Normally my join statement is as follows:

SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = co.CID

but this time the columns in the columns in the tables that has relation is similar but with one difference. C.ID = '123' co.CID = 'c123' so how can i join this;

SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = 'c' + co.CID

something like that :) is it possible? or i need to accomplish what i am trying in two different statements? Thank you very much for your help

[loyal ginger]

Your second join works on some systems. Did you try it on your system?

[wonder FOOL]

Yes I have tried but it is not accepting it. When i try it I am getting thois error:

Dynamic SQL Error
expression evaluation not supported
Strings cannot be added or subtracted in dialect 3

[wonder FOOL]

I have found the solution. The solution is SELECT c.Name, co.Name FROM City C JOIN Country co ON c.ID = ('s' || co.CID)

[Blue_Boy]

try this

SELECT c.Name, co.Name
FROM City C
inner JOIN Country co ON c.ID = co.CID
where C.ID = '123'
and co.CID = 'c123'

---

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

[loyal ginger]

Your query will return 0 rows.

[SilimSayo]

seriously?

[Pete OHanlon]

Have you really thought this one through? How can c.ID (and co.CID) equal 123 and c123 simultaneously? Have you come across some new quantum extension to T-SQL that allows uncertainty in equality tests?

Forgive your enemies - it messes with their heads

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

[Naunt]

Use substring

SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = Substring(co.CID,2,3)

[phome]

It seems u are using PL/SQL not T-SQL.

Good day,Good job,Good life

[Niladri_Biswas]

Try

'c' + c.ID = co.CID

and not

c.ID = 'c' + co.CID

If C.Id is of integer type then cast it

'c' + Cast(c.ID as varchar(20))= co.CID

Try this

Declare @City table (Id int,Name Varchar(20))
Insert Into @City
Select 123,'City1' Union All Select 1,'City2' Union ALl Select 124,'City3' Union ALl Select 5,'City4'

Declare @Country table (Id int,CID Varchar(10),Name Varchar(20))
Insert Into @Country
Select 1,'c123','Country 1' Union All
Select 1,'1','Country 1' Union All
Select 1,'c5','Country 1'

SELECT c.Name, co.Name
FROM @City C
JOIN @Country co ON 'c' + Cast(c.ID as varchar(20))= co.CID

Hope this helps

Niladri Biswas