Comments by RasikaLB (Top 2 by date)

select t.id, t.name,cast( cast(a.id as varchar) + cast(t.id as varchar)as varchar) as dn from tbemp t inner join a on t.did = a.id where a.dn like '%d'

i think this code can replace by following code without where part using tbdep table

select t.id, t.name,cast( cast(ab.id as varchar) + cast(t.id as varchar)as varchar) as dn from tbemp t inner join tbdep ab on t.did = a.id

btw nice work Amir i hvnt use "with" in sql thanks

bt heard it's only supported in SQL Server (2005+, called Common Table Expressions) and Oracle (9i+, called Subquery Factoring).