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

Rahul Prajapat · Answer

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.

Karthikeyan K · Answer

SQL> CREATE TABLE SAMPLE(ID NUMBERNAME VARCHAR(20)); Table created. SQL> CREATE TABLE SAMPLE1(ID NUMBERNAME VARCHAR(20)); Table created. SQL> INSERT INTO SAMPLE(IDNAME) VALUES('&ID''&NAME'); Enter value for id: 1 Enter value for name: KARTHIK old 1: INSERT INTO SAMPLE(IDNAME) VALUES('&ID''&NAME') new 1: INSERT INTO SAMPLE(IDNAME) VALUES('1''KARTHIK') 1 row created. SQL> / Enter value for id: 2 Enter value for name: MITHU old 1: INSERT INTO SAMPLE(IDNAME) VALUES('&ID''&NAME') new 1: INSERT INTO SAMPLE(IDNAME) VALUES('2''MITHU') 1 row created. SELECT IDNAME FROM(SELECT * FROM SAMPLE UNION ALL SELECT * FROM SAMPLE1) WHERE ID=2; SQL> INSERT INTO SAMPLE1(IDNAME) VALUES('&ID''&NAME'); Enter value for id: 3 Enter value for name: VINITH old 1: INSERT INTO SAMPLE1(IDNAME) VALUES('&ID''&NAME') new 1: INSERT INTO SAMPLE1(IDNAME) VALUES('3''VINITH') 1 row created. SQL> / Enter value for id: 4 Enter value for name: MENON old 1: INSERT INTO SAMPLE1(IDNAME) VALUES('&ID''&NAME') new 1: INSERT INTO SAMPLE1(IDNAME) VALUES('4''MENON') 1 row created. SQL> SELECT IDNAME FROM(SELECT * FROM SAMPLE UNION ALL SELECT * FROM SAMPLE1) WHERE ID=2; ID NAME ---------- -------------------- 2 MITHU

Vilas Dongre · Answer

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

Rajeesh Menoth · Answer

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

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