Simple Inner Join for comparing two values in table

Question

I'm currently stuck at figuring out what could be considered a rather basic and common inner join problem:

I have two tables orders and locations:

orders

|----------------------|-------------------|-------------------|
| foreign_from_id      | foreign_to_id     |    same_triade    |
|----------------------|-------------------|-------------------|
|          US          |        MX         |                   |
|          US          |        DE         |                   |
|----------------------|-------------------|-------------------|

locations

|----------------------|-------------------|
|       foreign_id     |       triade      |
|----------------------|-------------------|
|          US          |         AME       |
|          MX          |         AME       |
|          DE          |         EU        |
|----------------------|-------------------|

My code is supposed to check if the foreign_from_id and the foreign_to_id is in the same triade and return a 1 or 0 in same_triade. Helping me figuring out this one would help me solve a lot of my SQL problems.

Thanks in advance!

Answer 1

Starting from @PeterHe's query I wrote this:

UPDATE orders
INNER JOIN locations fl ON orders.foreign_from_id=fl.foreign_id 
INNER JOIN locations tl ON orders.foreign_to_id=tl.foreign_id 
SET orders.same_triade = (CASE WHEN fl.triade=tl.triade THEN 1 ELSE 0 END)

DEMO