CODEWITHSUNDEEP
sqlselect
user3217574
user3217574

Reputation: 19

Compare two tables and insert the matching content to another table

I have been working on this problem for 3 months now and gave up once or twice. Yes, I am a novice. I created 3 tables with data. Table 1 has a letter and number. Table 2 has a name, letter and number. Table 3 has the end result. I want to compare the T1 and T2. If the name and number in T1 matches a name and number in table 2. I want the result to in T3 to include name, letter and number. This is what I have so far but it is not working. 

SELECT * FROM T1 and SELECT * FROM T2
WHEN 
TABLE T1(letter) && TABLE T2(letter)
AND 
TABLE T1(number) && TABLE T2(letter)
INSERT INTO TABLE T3 (name,letter,number)

Upvotes: 1

Views: 2483

Answers (2)

domdomcodecode
domdomcodecode

Reputation: 2453

To expand upon Dinesh's answer,

You'll need an inner join for this. Inner joins gives you rows that match the columns in both tables you've specified.

You can then combine it into an insert statement to put it into your T3 table. So this is one complete SQL statement:

INSERT INTO T3
SELECT T2.name, T2.letter, T2.number
FROM T2
    INNER JOIN T1
    ON T2.letter = T1.letter
    AND T2.number = T1.number;

As a side note, there's also left joins and right joins (and heaps more). Think of Left, Inner, Right joins as two circles in a venn diagram.

Upvotes: 0

Dinesh
Dinesh

Reputation: 2204

What you need is to do an inner join of the first and second tables based on the attributes name and letter.

SELECT T2.name, T2.letter, T2.number
FROM T1
INNER JOIN T2
ON T1.letter=T2.letter AND T1.number=T2.number;

For more details, you can refer http://www.w3schools.com/sql/sql_join.asp

Upvotes: 1

Related Questions