Join two tables and create a new one in SQL I am new to SQL and trying to apply a concept of Joins in MS-access.

I have two tables Table 1 and Table 2:

I want a query which will create a Table 3:

Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'

Can someone please help me with this query. Thanks in advance.

I have attached the samples for your reference
You need to UNION ALL the two table entries
Then take the SUM(Category) by GROUP BY ID, Name


So the query will be

SELECT ID, Name, SUM(Category) AS Category
FROM  (
    SELECT ID, Name, Category FROM Table1 
    UNION ALL
    SELECT ID, Name, Category FROM Table2
) GROUP BY ID, Name


You can insert the entries into the third table.