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.enter image description hereenter image description here

I have attached the samples for your reference

  • What DBMS do you use? SQL Server, Postgres, Oracle, DB2, SQLite, Firebird, etc ...? And what if some rows match the ID but have different values in `Name? – ypercubeᵀᴹ Sep 28 '16 at 9:32
  • Will the categories in the source tables necessarily be 1 and 2? What if they are other values? What if one of them is already 3 and there's a match with a different category for the same ID,Name in the other table? – Andriy M Sep 29 '16 at 6:06
  • 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
    SELECT ID, Name, Category FROM Table1 
    SELECT ID, Name, Category FROM Table2

You can insert the entries into the third table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.