Search record in SQL Server Database Tables

Question

Dear All,

Please help me for the following query.....

Suppose I have 2 Tables in Database and I don't know the  particular Record but i want to search that record.

So I want a single SQL Query  through which i can search a Specific Record.

I am using SQL Server 2008 R2

 

Scenario:

 

Table1	Table 2
ID    RollNo    Name --------------------------------- 1       S-123       RAMESH 2       S-223       SURESH 3       S-133       VILAS 4       S-153       SACHIN 5       S-423       AMOL	ID    RollNo       Name ---------------------------------- 1      E-123       RAM 2      E-223       SURENDRA 3      E-133       KALPESH 4      E-153       SABIN 5      E-423       SHRIKANT

 

As per the above tables I want to search RollNo E-153 but should be from single query.

Please help me for the same.

 

Thanks in Advance