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

Answer 1

Hello @Vilas try this query:

 

SELECT * FROM (SELECT * FROM dbo.Table_1 t UNION SELECT * FROM dbo.Table_2 t)AS Student WHERE Student.roll='E-153'

 

I hope this will help you.

Answer 2

SQL> CREATE TABLE SAMPLE(ID NUMBER,NAME VARCHAR(20));

Table created.

SQL> CREATE TABLE SAMPLE1(ID NUMBER,NAME VARCHAR(20));

Table created.

SQL> INSERT INTO SAMPLE(ID,NAME) VALUES('&ID','&NAME');
Enter value for id: 1
Enter value for name: KARTHIK
old 1: INSERT INTO SAMPLE(ID,NAME) VALUES('&ID','&NAME')
new 1: INSERT INTO SAMPLE(ID,NAME) VALUES('1','KARTHIK')

1 row created.

SQL> /
Enter value for id: 2
Enter value for name: MITHU
old 1: INSERT INTO SAMPLE(ID,NAME) VALUES('&ID','&NAME')
new 1: INSERT INTO SAMPLE(ID,NAME) VALUES('2','MITHU')

1 row created.
SELECT ID,NAME FROM(SELECT * FROM SAMPLE UNION ALL SELECT * FROM SAMPLE1) WHERE ID=2;
SQL> INSERT INTO SAMPLE1(ID,NAME) VALUES('&ID','&NAME');
Enter value for id: 3
Enter value for name: VINITH
old 1: INSERT INTO SAMPLE1(ID,NAME) VALUES('&ID','&NAME')
new 1: INSERT INTO SAMPLE1(ID,NAME) VALUES('3','VINITH')

1 row created.

SQL> /
Enter value for id: 4
Enter value for name: MENON
old 1: INSERT INTO SAMPLE1(ID,NAME) VALUES('&ID','&NAME')
new 1: INSERT INTO SAMPLE1(ID,NAME) VALUES('4','MENON')

1 row created.

SQL> SELECT ID,NAME FROM(SELECT * FROM SAMPLE UNION ALL SELECT * FROM SAMPLE1) WHERE ID=2;

ID NAME
---------- --------------------
2 MITHU

Answer 3

Actually Person is the single table through which we can find but I want single query to search single record from multiple tables

Answer 4

Hi,

 

A UNION will return unique results, whereas a UNION ALL will return everything including duplicates. 

 

Ref this link : http://stackoverflow.com/questions/5682513/select-query-with-multiple-tables-in-sql-server