Merging two tables using SQL

I have two tables that I need to merge.

Table 1 is :

Table 2 has

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

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:

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