Join two tables and subtract multiple columns

Question

I am brand new to SQL. I am trying to join two tables and subtract column values from table_a from similar columns in table_b in MySQL. The column headings and row headings are identical in both tables.

Example:

Table_a Id_num Val1 Val2 Val3 nameA 10 20 30 nameB 40 50 60 Table_b Id_num Val1 Val2 Val3 nameA 20 35 50 nameB 60 65 85

What I want is a table that looks like this:

Id_num Val1 Val2 Val3 nameA 10 15 20 nameB 20 15 25

I've tried the following:

 SELECT Table_a.Id_num (Table_a.Val1 - Table_b.Val1) as Val1, (Table_a.Val2 - Table_b.Val2) as Val2, (Table_a.Val3 - Table_b.Val3) as Val3 FROM Table_a INNER JOIN Table_a on Table_a.Id_num = Table_b.Id_num;

All I can get from this is a syntax error that tells me that 'Table_a' is not a unique table/alias.

I am completely confused. Any help appreciated.

I'm not sure I understand what you're asking. Nowhere in my example did I type a lower case "t" for Table_a? — Richard
– Richard, Commented Nov 24, 2017 at 17:41
Look careful in code block of your post "FROM Table_a INNER JOIN Table_a". I think it is your typing mistake but If you really write code like this It is a big error. One more thing, I see answer below is quite good and it may be correct answer for you — Bùi Đức Khánh
– Bùi Đức Khánh, Commented Nov 24, 2017 at 17:45

Fleury26 · Accepted Answer · 2017-11-24 17:14:34Z

You're missing a coma in your SELECT and your join should be on Table_b

 SELECT Table_a.Id_num, (Table_a.Val1 - Table_b.Val1) as Val1, (Table_a.Val2 - Table_b.Val2) as Val2, (Table_a.Val3 - Table_b.Val3) as Val3 FROM Table_a INNER JOIN Table_b on Table_b.Id_num = Table_a.Id_num;

Collectives™ on Stack Overflow

Join two tables and subtract multiple columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related