SQL Server - Inner join query How to find value

Asked By narendra on 05-Jan-11 01:56 AM

Good afternoon Friends
How to do Find and check value in inner join query my statement is:
one table value is
di10452
second table value is
10452

so please sir how to find
Thanks And regards
Narendra

user 00009 replied to narendra on 05-Jan-11 02:04 AM

Hi narendra,

Can you post both tables and also how you want the output structure.I didnt understand your question properly.

Venkat K replied to narendra on 05-Jan-11 02:05 AM

Hi narendra,

I think you are trying to join two tables which are different values as a subset, try using function when joining tables

select * from table1

inner join table2

on RIGHT(table1.number,5)=table2.number

It will compare only the right 5 digits from table1 against table2.

Hope this helps!

Jatin Prajapati replied to narendra on 05-Jan-11 02:08 AM

Hi Narendra,

When you use two tables in inner join, there must be a common column in both the tables which relate the records.

Here you have not provided any column information for any of the table. But I assume that there is a column name

ID in one table and RelatedID in second table. so you inner join query will be like this.

Select * from tbl1 inner join tbl2 on RelatedID=ID;

Ali Rab Ali Allah replied to narendra on 05-Jan-11 05:10 AM

Hi narenda ,

FIRST YOU HAVE TO REMOVE EXTRA CHARACTERS FROM 'di10452' WHICH ARE 'di' then you apply inner join.

select * from table1 t1 (nolock) inner join table2 t2 on t1.col1 = SUBSTRING (t2.col1,3,LEN(t2.col1))

note the statement

SUBSTRING (t2.col1,3,LEN(t2.col1)) = SUBSTRING ( 'di10452', 3, LEN('di10452')) = 10452