0

I want to compare 2 my sql tables with 4 constraints such as firstname,lastname,dob and address.If some records match i want to save it in the new table.

I compared 2 tables with 1 constraint using this query 

  SELECT * FROM t1 WHERE t1.dob IN (SELECT t2.dob from t2)

Is there any query to do this job?Any Help will be appreciated.

Table 1 

First Name  Varchar
Last  Name  Varchar
Dob         Date
Address     varchar
Email       Varchar
City        Varchar
Country     Varchar


Table2

First Name  Varchar
Last  Name  Varchar
Dob         Date
Address     varchar

If records matches in these tables I want to save the following Details in Third table as 

Table3
First Name  Varchar
Last  Name  Varchar
Dob         Date
Address     varchar
Email       Varchar
City        Varchar
Country     Varchar
Michael Härtl
  • 8,428
  • 5
  • 35
  • 62
Bharu
  • 191
  • 1
  • 3
  • 19

2 Answers2

0

You can use joins 

 INSERT INTO Table3 SELECT t1.First_Name,
t1.Last_Name,
t1.Dob  ,     
t1.Address   ,
t1.Email    , 
t1.City  ,    
t1.Country   
 FROM t1 inner join t2 on t1.dob=t2.dob
skv
  • 1,793
  • 3
  • 19
  • 27
0

The following code should do the trick for you:

insert into table3
(`first Name`, `last Name`, DOB, Address, Email, City, Country)
values
(select
    t1.`first Name`,
    t1.`last Name`,
    t1.DOB,
    t1.Address,
    t1.Email,
    t1.City,
    t1.Country
from
    `table 1` t1
        join `Table 2` t2
            on t1.DOB=t2.DOB)

But you would also do well to read this article I wrote on SQL that goes into much much more detail that this snippet and tries to explain a lot of the concepts of joins and other functions in detail.

Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • Are u sure?Iam getting an Sql syntax error in the Select statement in the query. – Bharu Aug 27 '13 at 11:44
  • Make sure your field names and table names are spelled correctly, and if escaped with a single quote, make sure that they are capitalized correctly. I am not certain that you gave us the correct table and field names for your example. – jmarkmurphy Aug 27 '13 at 14:21