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.

CodePudding user response：

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.

CodePudding user response：

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: