Merging two tables using SQL

I have two tables that I need to merge.

Table 1 is :

IDProduct codeSpend
1101100
1102200
1103300
2201400
3301500
3302600

Table 2 has

IDProduct codeSpendProduct tenure
110110020
110220030
330260040

I want to merge these such that only ID’s present in table 2 are retained from table 1. Table 2 does not contain all the product codes for each ID, but I want my final table to have it.

Output must have

IDProduct codeSpendProduct tenure
110110020
110220030
1103300
3301500
330260040

Any help on this would be appreciated. I tried left join on ID, but it produces many duplicates.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT  *,
    (
        SELECT `product_tenure` 
        FROM `second_table` 
        WHERE `second_table`.`id` = `first_table`.`id` 
        AND `first_table`.`product_code` = `second_table`.`product_code`
    ) product_tenure
FROM    `first_table`
WHERE   `id` IN (SELECT DISTINCT `id` FROM `second_table`)

Explaination:

Select id from second table, which wanted to keep from first table.

Because the product_tenure only in second_table select them with combined id and product_code

Result:

Merging two tables using SQL

Method 2

Test this:

SELECT *
FROM t1
LEFT JOIN t2 AS t21 USING (ID, `Product code`)
WHERE EXISTS ( SELECT NULL
               FROM t2 AS t22
               WHERE t1.ID = t22.ID )

PS. The query will return 2 Spent columns (they’re present in each table, and nothing prevents their values to be not equal…) – so replace an asterisk with definite columns list.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x